1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14import datetime
  15import math
  16import numbers
  17import re
  18import textwrap
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from decimal import Decimal
  23from enum import auto
  24from functools import reduce
  25
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34    to_bool,
  35)
  36from sqlglot.tokens import Token, TokenError
  37
  38if t.TYPE_CHECKING:
  39    from typing_extensions import Self
  40    from sqlglot._typing import E, Lit
  41    from sqlglot.dialects.dialect import DialectType
  42
  43    Q = t.TypeVar("Q", bound="Query")
  44    S = t.TypeVar("S", bound="SetOperation")
  45
  46
  47class _Expression(type):
  48    def __new__(cls, clsname, bases, attrs):
  49        klass = super().__new__(cls, clsname, bases, attrs)
  50
  51        # When an Expression class is created, its key is automatically set to be
  52        # the lowercase version of the class' name.
  53        klass.key = clsname.lower()
  54
  55        # This is so that docstrings are not inherited in pdoc
  56        klass.__doc__ = klass.__doc__ or ""
  57
  58        return klass
  59
  60
  61SQLGLOT_META = "sqlglot.meta"
  62SQLGLOT_ANONYMOUS = "sqlglot.anonymous"
  63TABLE_PARTS = ("this", "db", "catalog")
  64COLUMN_PARTS = ("this", "table", "db", "catalog")
  65
  66
  67class Expression(metaclass=_Expression):
  68    """
  69    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  70    context, such as its child expressions, their names (arg keys), and whether a given child expression
  71    is optional or not.
  72
  73    Attributes:
  74        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  75            and representing expressions as strings.
  76        arg_types: determines the arguments (child nodes) supported by an expression. It maps
  77            arg keys to booleans that indicate whether the corresponding args are optional.
  78        parent: a reference to the parent expression (or None, in case of root expressions).
  79        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  80            uses to refer to it.
  81        index: the index of an expression if it is inside of a list argument in its parent.
  82        comments: a list of comments that are associated with a given expression. This is used in
  83            order to preserve comments when transpiling SQL code.
  84        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  85            optimizer, in order to enable some transformations that require type information.
  86        meta: a dictionary that can be used to store useful metadata for a given expression.
  87
  88    Example:
  89        >>> class Foo(Expression):
  90        ...     arg_types = {"this": True, "expression": False}
  91
  92        The above definition informs us that Foo is an Expression that requires an argument called
  93        "this" and may also optionally receive an argument called "expression".
  94
  95    Args:
  96        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  97    """
  98
  99    key = "expression"
 100    arg_types = {"this": True}
 101    __slots__ = ("args", "parent", "arg_key", "index", "comments", "_type", "_meta", "_hash")
 102
 103    def __init__(self, **args: t.Any):
 104        self.args: t.Dict[str, t.Any] = args
 105        self.parent: t.Optional[Expression] = None
 106        self.arg_key: t.Optional[str] = None
 107        self.index: t.Optional[int] = None
 108        self.comments: t.Optional[t.List[str]] = None
 109        self._type: t.Optional[DataType] = None
 110        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 111        self._hash: t.Optional[int] = None
 112
 113        for arg_key, value in self.args.items():
 114            self._set_parent(arg_key, value)
 115
 116    def __eq__(self, other) -> bool:
 117        return type(self) is type(other) and hash(self) == hash(other)
 118
 119    @property
 120    def hashable_args(self) -> t.Any:
 121        return frozenset(
 122            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 123            for k, v in self.args.items()
 124            if not (v is None or v is False or (type(v) is list and not v))
 125        )
 126
 127    def __hash__(self) -> int:
 128        if self._hash is not None:
 129            return self._hash
 130
 131        return hash((self.__class__, self.hashable_args))
 132
 133    @property
 134    def this(self) -> t.Any:
 135        """
 136        Retrieves the argument with key "this".
 137        """
 138        return self.args.get("this")
 139
 140    @property
 141    def expression(self) -> t.Any:
 142        """
 143        Retrieves the argument with key "expression".
 144        """
 145        return self.args.get("expression")
 146
 147    @property
 148    def expressions(self) -> t.List[t.Any]:
 149        """
 150        Retrieves the argument with key "expressions".
 151        """
 152        return self.args.get("expressions") or []
 153
 154    def text(self, key) -> str:
 155        """
 156        Returns a textual representation of the argument corresponding to "key". This can only be used
 157        for args that are strings or leaf Expression instances, such as identifiers and literals.
 158        """
 159        field = self.args.get(key)
 160        if isinstance(field, str):
 161            return field
 162        if isinstance(field, (Identifier, Literal, Var)):
 163            return field.this
 164        if isinstance(field, (Star, Null)):
 165            return field.name
 166        return ""
 167
 168    @property
 169    def is_string(self) -> bool:
 170        """
 171        Checks whether a Literal expression is a string.
 172        """
 173        return isinstance(self, Literal) and self.args["is_string"]
 174
 175    @property
 176    def is_number(self) -> bool:
 177        """
 178        Checks whether a Literal expression is a number.
 179        """
 180        return (isinstance(self, Literal) and not self.args["is_string"]) or (
 181            isinstance(self, Neg) and self.this.is_number
 182        )
 183
 184    def to_py(self) -> t.Any:
 185        """
 186        Returns a Python object equivalent of the SQL node.
 187        """
 188        raise ValueError(f"{self} cannot be converted to a Python object.")
 189
 190    @property
 191    def is_int(self) -> bool:
 192        """
 193        Checks whether an expression is an integer.
 194        """
 195        return self.is_number and isinstance(self.to_py(), int)
 196
 197    @property
 198    def is_star(self) -> bool:
 199        """Checks whether an expression is a star."""
 200        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 201
 202    @property
 203    def alias(self) -> str:
 204        """
 205        Returns the alias of the expression, or an empty string if it's not aliased.
 206        """
 207        if isinstance(self.args.get("alias"), TableAlias):
 208            return self.args["alias"].name
 209        return self.text("alias")
 210
 211    @property
 212    def alias_column_names(self) -> t.List[str]:
 213        table_alias = self.args.get("alias")
 214        if not table_alias:
 215            return []
 216        return [c.name for c in table_alias.args.get("columns") or []]
 217
 218    @property
 219    def name(self) -> str:
 220        return self.text("this")
 221
 222    @property
 223    def alias_or_name(self) -> str:
 224        return self.alias or self.name
 225
 226    @property
 227    def output_name(self) -> str:
 228        """
 229        Name of the output column if this expression is a selection.
 230
 231        If the Expression has no output name, an empty string is returned.
 232
 233        Example:
 234            >>> from sqlglot import parse_one
 235            >>> parse_one("SELECT a").expressions[0].output_name
 236            'a'
 237            >>> parse_one("SELECT b AS c").expressions[0].output_name
 238            'c'
 239            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 240            ''
 241        """
 242        return ""
 243
 244    @property
 245    def type(self) -> t.Optional[DataType]:
 246        return self._type
 247
 248    @type.setter
 249    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 250        if dtype and not isinstance(dtype, DataType):
 251            dtype = DataType.build(dtype)
 252        self._type = dtype  # type: ignore
 253
 254    def is_type(self, *dtypes) -> bool:
 255        return self.type is not None and self.type.is_type(*dtypes)
 256
 257    def is_leaf(self) -> bool:
 258        return not any(isinstance(v, (Expression, list)) for v in self.args.values())
 259
 260    @property
 261    def meta(self) -> t.Dict[str, t.Any]:
 262        if self._meta is None:
 263            self._meta = {}
 264        return self._meta
 265
 266    def __deepcopy__(self, memo):
 267        root = self.__class__()
 268        stack = [(self, root)]
 269
 270        while stack:
 271            node, copy = stack.pop()
 272
 273            if node.comments is not None:
 274                copy.comments = deepcopy(node.comments)
 275            if node._type is not None:
 276                copy._type = deepcopy(node._type)
 277            if node._meta is not None:
 278                copy._meta = deepcopy(node._meta)
 279            if node._hash is not None:
 280                copy._hash = node._hash
 281
 282            for k, vs in node.args.items():
 283                if hasattr(vs, "parent"):
 284                    stack.append((vs, vs.__class__()))
 285                    copy.set(k, stack[-1][-1])
 286                elif type(vs) is list:
 287                    copy.args[k] = []
 288
 289                    for v in vs:
 290                        if hasattr(v, "parent"):
 291                            stack.append((v, v.__class__()))
 292                            copy.append(k, stack[-1][-1])
 293                        else:
 294                            copy.append(k, v)
 295                else:
 296                    copy.args[k] = vs
 297
 298        return root
 299
 300    def copy(self) -> Self:
 301        """
 302        Returns a deep copy of the expression.
 303        """
 304        return deepcopy(self)
 305
 306    def add_comments(self, comments: t.Optional[t.List[str]] = None, prepend: bool = False) -> None:
 307        if self.comments is None:
 308            self.comments = []
 309
 310        if comments:
 311            for comment in comments:
 312                _, *meta = comment.split(SQLGLOT_META)
 313                if meta:
 314                    for kv in "".join(meta).split(","):
 315                        k, *v = kv.split("=")
 316                        value = v[0].strip() if v else True
 317                        self.meta[k.strip()] = to_bool(value)
 318
 319                if not prepend:
 320                    self.comments.append(comment)
 321
 322            if prepend:
 323                self.comments = comments + self.comments
 324
 325    def pop_comments(self) -> t.List[str]:
 326        comments = self.comments or []
 327        self.comments = None
 328        return comments
 329
 330    def append(self, arg_key: str, value: t.Any) -> None:
 331        """
 332        Appends value to arg_key if it's a list or sets it as a new list.
 333
 334        Args:
 335            arg_key (str): name of the list expression arg
 336            value (Any): value to append to the list
 337        """
 338        if type(self.args.get(arg_key)) is not list:
 339            self.args[arg_key] = []
 340        self._set_parent(arg_key, value)
 341        values = self.args[arg_key]
 342        if hasattr(value, "parent"):
 343            value.index = len(values)
 344        values.append(value)
 345
 346    def set(
 347        self,
 348        arg_key: str,
 349        value: t.Any,
 350        index: t.Optional[int] = None,
 351        overwrite: bool = True,
 352    ) -> None:
 353        """
 354        Sets arg_key to value.
 355
 356        Args:
 357            arg_key: name of the expression arg.
 358            value: value to set the arg to.
 359            index: if the arg is a list, this specifies what position to add the value in it.
 360            overwrite: assuming an index is given, this determines whether to overwrite the
 361                list entry instead of only inserting a new value (i.e., like list.insert).
 362        """
 363        if index is not None:
 364            expressions = self.args.get(arg_key) or []
 365
 366            if seq_get(expressions, index) is None:
 367                return
 368            if value is None:
 369                expressions.pop(index)
 370                for v in expressions[index:]:
 371                    v.index = v.index - 1
 372                return
 373
 374            if isinstance(value, list):
 375                expressions.pop(index)
 376                expressions[index:index] = value
 377            elif overwrite:
 378                expressions[index] = value
 379            else:
 380                expressions.insert(index, value)
 381
 382            value = expressions
 383        elif value is None:
 384            self.args.pop(arg_key, None)
 385            return
 386
 387        self.args[arg_key] = value
 388        self._set_parent(arg_key, value, index)
 389
 390    def _set_parent(self, arg_key: str, value: t.Any, index: t.Optional[int] = None) -> None:
 391        if hasattr(value, "parent"):
 392            value.parent = self
 393            value.arg_key = arg_key
 394            value.index = index
 395        elif type(value) is list:
 396            for index, v in enumerate(value):
 397                if hasattr(v, "parent"):
 398                    v.parent = self
 399                    v.arg_key = arg_key
 400                    v.index = index
 401
 402    @property
 403    def depth(self) -> int:
 404        """
 405        Returns the depth of this tree.
 406        """
 407        if self.parent:
 408            return self.parent.depth + 1
 409        return 0
 410
 411    def iter_expressions(self, reverse: bool = False) -> t.Iterator[Expression]:
 412        """Yields the key and expression for all arguments, exploding list args."""
 413        # remove tuple when python 3.7 is deprecated
 414        for vs in reversed(tuple(self.args.values())) if reverse else self.args.values():  # type: ignore
 415            if type(vs) is list:
 416                for v in reversed(vs) if reverse else vs:  # type: ignore
 417                    if hasattr(v, "parent"):
 418                        yield v
 419            else:
 420                if hasattr(vs, "parent"):
 421                    yield vs
 422
 423    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 424        """
 425        Returns the first node in this tree which matches at least one of
 426        the specified types.
 427
 428        Args:
 429            expression_types: the expression type(s) to match.
 430            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 431
 432        Returns:
 433            The node which matches the criteria or None if no such node was found.
 434        """
 435        return next(self.find_all(*expression_types, bfs=bfs), None)
 436
 437    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 438        """
 439        Returns a generator object which visits all nodes in this tree and only
 440        yields those that match at least one of the specified expression types.
 441
 442        Args:
 443            expression_types: the expression type(s) to match.
 444            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 445
 446        Returns:
 447            The generator object.
 448        """
 449        for expression in self.walk(bfs=bfs):
 450            if isinstance(expression, expression_types):
 451                yield expression
 452
 453    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 454        """
 455        Returns a nearest parent matching expression_types.
 456
 457        Args:
 458            expression_types: the expression type(s) to match.
 459
 460        Returns:
 461            The parent node.
 462        """
 463        ancestor = self.parent
 464        while ancestor and not isinstance(ancestor, expression_types):
 465            ancestor = ancestor.parent
 466        return ancestor  # type: ignore
 467
 468    @property
 469    def parent_select(self) -> t.Optional[Select]:
 470        """
 471        Returns the parent select statement.
 472        """
 473        return self.find_ancestor(Select)
 474
 475    @property
 476    def same_parent(self) -> bool:
 477        """Returns if the parent is the same class as itself."""
 478        return type(self.parent) is self.__class__
 479
 480    def root(self) -> Expression:
 481        """
 482        Returns the root expression of this tree.
 483        """
 484        expression = self
 485        while expression.parent:
 486            expression = expression.parent
 487        return expression
 488
 489    def walk(
 490        self, bfs: bool = True, prune: t.Optional[t.Callable[[Expression], bool]] = None
 491    ) -> t.Iterator[Expression]:
 492        """
 493        Returns a generator object which visits all nodes in this tree.
 494
 495        Args:
 496            bfs: if set to True the BFS traversal order will be applied,
 497                otherwise the DFS traversal will be used instead.
 498            prune: callable that returns True if the generator should stop traversing
 499                this branch of the tree.
 500
 501        Returns:
 502            the generator object.
 503        """
 504        if bfs:
 505            yield from self.bfs(prune=prune)
 506        else:
 507            yield from self.dfs(prune=prune)
 508
 509    def dfs(
 510        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 511    ) -> t.Iterator[Expression]:
 512        """
 513        Returns a generator object which visits all nodes in this tree in
 514        the DFS (Depth-first) order.
 515
 516        Returns:
 517            The generator object.
 518        """
 519        stack = [self]
 520
 521        while stack:
 522            node = stack.pop()
 523
 524            yield node
 525
 526            if prune and prune(node):
 527                continue
 528
 529            for v in node.iter_expressions(reverse=True):
 530                stack.append(v)
 531
 532    def bfs(
 533        self, prune: t.Optional[t.Callable[[Expression], bool]] = None
 534    ) -> t.Iterator[Expression]:
 535        """
 536        Returns a generator object which visits all nodes in this tree in
 537        the BFS (Breadth-first) order.
 538
 539        Returns:
 540            The generator object.
 541        """
 542        queue = deque([self])
 543
 544        while queue:
 545            node = queue.popleft()
 546
 547            yield node
 548
 549            if prune and prune(node):
 550                continue
 551
 552            for v in node.iter_expressions():
 553                queue.append(v)
 554
 555    def unnest(self):
 556        """
 557        Returns the first non parenthesis child or self.
 558        """
 559        expression = self
 560        while type(expression) is Paren:
 561            expression = expression.this
 562        return expression
 563
 564    def unalias(self):
 565        """
 566        Returns the inner expression if this is an Alias.
 567        """
 568        if isinstance(self, Alias):
 569            return self.this
 570        return self
 571
 572    def unnest_operands(self):
 573        """
 574        Returns unnested operands as a tuple.
 575        """
 576        return tuple(arg.unnest() for arg in self.iter_expressions())
 577
 578    def flatten(self, unnest=True):
 579        """
 580        Returns a generator which yields child nodes whose parents are the same class.
 581
 582        A AND B AND C -> [A, B, C]
 583        """
 584        for node in self.dfs(prune=lambda n: n.parent and type(n) is not self.__class__):
 585            if type(node) is not self.__class__:
 586                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 587
 588    def __str__(self) -> str:
 589        return self.sql()
 590
 591    def __repr__(self) -> str:
 592        return _to_s(self)
 593
 594    def to_s(self) -> str:
 595        """
 596        Same as __repr__, but includes additional information which can be useful
 597        for debugging, like empty or missing args and the AST nodes' object IDs.
 598        """
 599        return _to_s(self, verbose=True)
 600
 601    def sql(self, dialect: DialectType = None, **opts) -> str:
 602        """
 603        Returns SQL string representation of this tree.
 604
 605        Args:
 606            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 607            opts: other `sqlglot.generator.Generator` options.
 608
 609        Returns:
 610            The SQL string.
 611        """
 612        from sqlglot.dialects import Dialect
 613
 614        return Dialect.get_or_raise(dialect).generate(self, **opts)
 615
 616    def transform(self, fun: t.Callable, *args: t.Any, copy: bool = True, **kwargs) -> Expression:
 617        """
 618        Visits all tree nodes (excluding already transformed ones)
 619        and applies the given transformation function to each node.
 620
 621        Args:
 622            fun: a function which takes a node as an argument and returns a
 623                new transformed node or the same node without modifications. If the function
 624                returns None, then the corresponding node will be removed from the syntax tree.
 625            copy: if set to True a new tree instance is constructed, otherwise the tree is
 626                modified in place.
 627
 628        Returns:
 629            The transformed tree.
 630        """
 631        root = None
 632        new_node = None
 633
 634        for node in (self.copy() if copy else self).dfs(prune=lambda n: n is not new_node):
 635            parent, arg_key, index = node.parent, node.arg_key, node.index
 636            new_node = fun(node, *args, **kwargs)
 637
 638            if not root:
 639                root = new_node
 640            elif parent and arg_key and new_node is not node:
 641                parent.set(arg_key, new_node, index)
 642
 643        assert root
 644        return root.assert_is(Expression)
 645
 646    @t.overload
 647    def replace(self, expression: E) -> E: ...
 648
 649    @t.overload
 650    def replace(self, expression: None) -> None: ...
 651
 652    def replace(self, expression):
 653        """
 654        Swap out this expression with a new expression.
 655
 656        For example::
 657
 658            >>> tree = Select().select("x").from_("tbl")
 659            >>> tree.find(Column).replace(column("y"))
 660            Column(
 661              this=Identifier(this=y, quoted=False))
 662            >>> tree.sql()
 663            'SELECT y FROM tbl'
 664
 665        Args:
 666            expression: new node
 667
 668        Returns:
 669            The new expression or expressions.
 670        """
 671        parent = self.parent
 672
 673        if not parent or parent is expression:
 674            return expression
 675
 676        key = self.arg_key
 677        value = parent.args.get(key)
 678
 679        if type(expression) is list and isinstance(value, Expression):
 680            # We are trying to replace an Expression with a list, so it's assumed that
 681            # the intention was to really replace the parent of this expression.
 682            value.parent.replace(expression)
 683        else:
 684            parent.set(key, expression, self.index)
 685
 686        if expression is not self:
 687            self.parent = None
 688            self.arg_key = None
 689            self.index = None
 690
 691        return expression
 692
 693    def pop(self: E) -> E:
 694        """
 695        Remove this expression from its AST.
 696
 697        Returns:
 698            The popped expression.
 699        """
 700        self.replace(None)
 701        return self
 702
 703    def assert_is(self, type_: t.Type[E]) -> E:
 704        """
 705        Assert that this `Expression` is an instance of `type_`.
 706
 707        If it is NOT an instance of `type_`, this raises an assertion error.
 708        Otherwise, this returns this expression.
 709
 710        Examples:
 711            This is useful for type security in chained expressions:
 712
 713            >>> import sqlglot
 714            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 715            'SELECT x, z FROM y'
 716        """
 717        if not isinstance(self, type_):
 718            raise AssertionError(f"{self} is not {type_}.")
 719        return self
 720
 721    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 722        """
 723        Checks if this expression is valid (e.g. all mandatory args are set).
 724
 725        Args:
 726            args: a sequence of values that were used to instantiate a Func expression. This is used
 727                to check that the provided arguments don't exceed the function argument limit.
 728
 729        Returns:
 730            A list of error messages for all possible errors that were found.
 731        """
 732        errors: t.List[str] = []
 733
 734        for k in self.args:
 735            if k not in self.arg_types:
 736                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 737        for k, mandatory in self.arg_types.items():
 738            v = self.args.get(k)
 739            if mandatory and (v is None or (isinstance(v, list) and not v)):
 740                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 741
 742        if (
 743            args
 744            and isinstance(self, Func)
 745            and len(args) > len(self.arg_types)
 746            and not self.is_var_len_args
 747        ):
 748            errors.append(
 749                f"The number of provided arguments ({len(args)}) is greater than "
 750                f"the maximum number of supported arguments ({len(self.arg_types)})"
 751            )
 752
 753        return errors
 754
 755    def dump(self):
 756        """
 757        Dump this Expression to a JSON-serializable dict.
 758        """
 759        from sqlglot.serde import dump
 760
 761        return dump(self)
 762
 763    @classmethod
 764    def load(cls, obj):
 765        """
 766        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 767        """
 768        from sqlglot.serde import load
 769
 770        return load(obj)
 771
 772    def and_(
 773        self,
 774        *expressions: t.Optional[ExpOrStr],
 775        dialect: DialectType = None,
 776        copy: bool = True,
 777        wrap: bool = True,
 778        **opts,
 779    ) -> Condition:
 780        """
 781        AND this condition with one or multiple expressions.
 782
 783        Example:
 784            >>> condition("x=1").and_("y=1").sql()
 785            'x = 1 AND y = 1'
 786
 787        Args:
 788            *expressions: the SQL code strings to parse.
 789                If an `Expression` instance is passed, it will be used as-is.
 790            dialect: the dialect used to parse the input expression.
 791            copy: whether to copy the involved expressions (only applies to Expressions).
 792            wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid
 793                precedence issues, but can be turned off when the produced AST is too deep and
 794                causes recursion-related issues.
 795            opts: other options to use to parse the input expressions.
 796
 797        Returns:
 798            The new And condition.
 799        """
 800        return and_(self, *expressions, dialect=dialect, copy=copy, wrap=wrap, **opts)
 801
 802    def or_(
 803        self,
 804        *expressions: t.Optional[ExpOrStr],
 805        dialect: DialectType = None,
 806        copy: bool = True,
 807        wrap: bool = True,
 808        **opts,
 809    ) -> Condition:
 810        """
 811        OR this condition with one or multiple expressions.
 812
 813        Example:
 814            >>> condition("x=1").or_("y=1").sql()
 815            'x = 1 OR y = 1'
 816
 817        Args:
 818            *expressions: the SQL code strings to parse.
 819                If an `Expression` instance is passed, it will be used as-is.
 820            dialect: the dialect used to parse the input expression.
 821            copy: whether to copy the involved expressions (only applies to Expressions).
 822            wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid
 823                precedence issues, but can be turned off when the produced AST is too deep and
 824                causes recursion-related issues.
 825            opts: other options to use to parse the input expressions.
 826
 827        Returns:
 828            The new Or condition.
 829        """
 830        return or_(self, *expressions, dialect=dialect, copy=copy, wrap=wrap, **opts)
 831
 832    def not_(self, copy: bool = True):
 833        """
 834        Wrap this condition with NOT.
 835
 836        Example:
 837            >>> condition("x=1").not_().sql()
 838            'NOT x = 1'
 839
 840        Args:
 841            copy: whether to copy this object.
 842
 843        Returns:
 844            The new Not instance.
 845        """
 846        return not_(self, copy=copy)
 847
 848    def as_(
 849        self,
 850        alias: str | Identifier,
 851        quoted: t.Optional[bool] = None,
 852        dialect: DialectType = None,
 853        copy: bool = True,
 854        **opts,
 855    ) -> Alias:
 856        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 857
 858    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 859        this = self.copy()
 860        other = convert(other, copy=True)
 861        if not isinstance(this, klass) and not isinstance(other, klass):
 862            this = _wrap(this, Binary)
 863            other = _wrap(other, Binary)
 864        if reverse:
 865            return klass(this=other, expression=this)
 866        return klass(this=this, expression=other)
 867
 868    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 869        return Bracket(
 870            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 871        )
 872
 873    def __iter__(self) -> t.Iterator:
 874        if "expressions" in self.arg_types:
 875            return iter(self.args.get("expressions") or [])
 876        # We define this because __getitem__ converts Expression into an iterable, which is
 877        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 878        # See: https://peps.python.org/pep-0234/
 879        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 880
 881    def isin(
 882        self,
 883        *expressions: t.Any,
 884        query: t.Optional[ExpOrStr] = None,
 885        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 886        copy: bool = True,
 887        **opts,
 888    ) -> In:
 889        subquery = maybe_parse(query, copy=copy, **opts) if query else None
 890        if subquery and not isinstance(subquery, Subquery):
 891            subquery = subquery.subquery(copy=False)
 892
 893        return In(
 894            this=maybe_copy(self, copy),
 895            expressions=[convert(e, copy=copy) for e in expressions],
 896            query=subquery,
 897            unnest=(
 898                Unnest(
 899                    expressions=[
 900                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
 901                        for e in ensure_list(unnest)
 902                    ]
 903                )
 904                if unnest
 905                else None
 906            ),
 907        )
 908
 909    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 910        return Between(
 911            this=maybe_copy(self, copy),
 912            low=convert(low, copy=copy, **opts),
 913            high=convert(high, copy=copy, **opts),
 914        )
 915
 916    def is_(self, other: ExpOrStr) -> Is:
 917        return self._binop(Is, other)
 918
 919    def like(self, other: ExpOrStr) -> Like:
 920        return self._binop(Like, other)
 921
 922    def ilike(self, other: ExpOrStr) -> ILike:
 923        return self._binop(ILike, other)
 924
 925    def eq(self, other: t.Any) -> EQ:
 926        return self._binop(EQ, other)
 927
 928    def neq(self, other: t.Any) -> NEQ:
 929        return self._binop(NEQ, other)
 930
 931    def rlike(self, other: ExpOrStr) -> RegexpLike:
 932        return self._binop(RegexpLike, other)
 933
 934    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 935        div = self._binop(Div, other)
 936        div.args["typed"] = typed
 937        div.args["safe"] = safe
 938        return div
 939
 940    def asc(self, nulls_first: bool = True) -> Ordered:
 941        return Ordered(this=self.copy(), nulls_first=nulls_first)
 942
 943    def desc(self, nulls_first: bool = False) -> Ordered:
 944        return Ordered(this=self.copy(), desc=True, nulls_first=nulls_first)
 945
 946    def __lt__(self, other: t.Any) -> LT:
 947        return self._binop(LT, other)
 948
 949    def __le__(self, other: t.Any) -> LTE:
 950        return self._binop(LTE, other)
 951
 952    def __gt__(self, other: t.Any) -> GT:
 953        return self._binop(GT, other)
 954
 955    def __ge__(self, other: t.Any) -> GTE:
 956        return self._binop(GTE, other)
 957
 958    def __add__(self, other: t.Any) -> Add:
 959        return self._binop(Add, other)
 960
 961    def __radd__(self, other: t.Any) -> Add:
 962        return self._binop(Add, other, reverse=True)
 963
 964    def __sub__(self, other: t.Any) -> Sub:
 965        return self._binop(Sub, other)
 966
 967    def __rsub__(self, other: t.Any) -> Sub:
 968        return self._binop(Sub, other, reverse=True)
 969
 970    def __mul__(self, other: t.Any) -> Mul:
 971        return self._binop(Mul, other)
 972
 973    def __rmul__(self, other: t.Any) -> Mul:
 974        return self._binop(Mul, other, reverse=True)
 975
 976    def __truediv__(self, other: t.Any) -> Div:
 977        return self._binop(Div, other)
 978
 979    def __rtruediv__(self, other: t.Any) -> Div:
 980        return self._binop(Div, other, reverse=True)
 981
 982    def __floordiv__(self, other: t.Any) -> IntDiv:
 983        return self._binop(IntDiv, other)
 984
 985    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 986        return self._binop(IntDiv, other, reverse=True)
 987
 988    def __mod__(self, other: t.Any) -> Mod:
 989        return self._binop(Mod, other)
 990
 991    def __rmod__(self, other: t.Any) -> Mod:
 992        return self._binop(Mod, other, reverse=True)
 993
 994    def __pow__(self, other: t.Any) -> Pow:
 995        return self._binop(Pow, other)
 996
 997    def __rpow__(self, other: t.Any) -> Pow:
 998        return self._binop(Pow, other, reverse=True)
 999
1000    def __and__(self, other: t.Any) -> And:
1001        return self._binop(And, other)
1002
1003    def __rand__(self, other: t.Any) -> And:
1004        return self._binop(And, other, reverse=True)
1005
1006    def __or__(self, other: t.Any) -> Or:
1007        return self._binop(Or, other)
1008
1009    def __ror__(self, other: t.Any) -> Or:
1010        return self._binop(Or, other, reverse=True)
1011
1012    def __neg__(self) -> Neg:
1013        return Neg(this=_wrap(self.copy(), Binary))
1014
1015    def __invert__(self) -> Not:
1016        return not_(self.copy())
1017
1018
1019IntoType = t.Union[
1020    str,
1021    t.Type[Expression],
1022    t.Collection[t.Union[str, t.Type[Expression]]],
1023]
1024ExpOrStr = t.Union[str, Expression]
1025
1026
1027class Condition(Expression):
1028    """Logical conditions like x AND y, or simply x"""
1029
1030
1031class Predicate(Condition):
1032    """Relationships like x = y, x > 1, x >= y."""
1033
1034
1035class DerivedTable(Expression):
1036    @property
1037    def selects(self) -> t.List[Expression]:
1038        return self.this.selects if isinstance(self.this, Query) else []
1039
1040    @property
1041    def named_selects(self) -> t.List[str]:
1042        return [select.output_name for select in self.selects]
1043
1044
1045class Query(Expression):
1046    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
1047        """
1048        Returns a `Subquery` that wraps around this query.
1049
1050        Example:
1051            >>> subquery = Select().select("x").from_("tbl").subquery()
1052            >>> Select().select("x").from_(subquery).sql()
1053            'SELECT x FROM (SELECT x FROM tbl)'
1054
1055        Args:
1056            alias: an optional alias for the subquery.
1057            copy: if `False`, modify this expression instance in-place.
1058        """
1059        instance = maybe_copy(self, copy)
1060        if not isinstance(alias, Expression):
1061            alias = TableAlias(this=to_identifier(alias)) if alias else None
1062
1063        return Subquery(this=instance, alias=alias)
1064
1065    def limit(
1066        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1067    ) -> Q:
1068        """
1069        Adds a LIMIT clause to this query.
1070
1071        Example:
1072            >>> select("1").union(select("1")).limit(1).sql()
1073            'SELECT 1 UNION SELECT 1 LIMIT 1'
1074
1075        Args:
1076            expression: the SQL code string to parse.
1077                This can also be an integer.
1078                If a `Limit` instance is passed, it will be used as-is.
1079                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
1080            dialect: the dialect used to parse the input expression.
1081            copy: if `False`, modify this expression instance in-place.
1082            opts: other options to use to parse the input expressions.
1083
1084        Returns:
1085            A limited Select expression.
1086        """
1087        return _apply_builder(
1088            expression=expression,
1089            instance=self,
1090            arg="limit",
1091            into=Limit,
1092            prefix="LIMIT",
1093            dialect=dialect,
1094            copy=copy,
1095            into_arg="expression",
1096            **opts,
1097        )
1098
1099    def offset(
1100        self: Q, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
1101    ) -> Q:
1102        """
1103        Set the OFFSET expression.
1104
1105        Example:
1106            >>> Select().from_("tbl").select("x").offset(10).sql()
1107            'SELECT x FROM tbl OFFSET 10'
1108
1109        Args:
1110            expression: the SQL code string to parse.
1111                This can also be an integer.
1112                If a `Offset` instance is passed, this is used as-is.
1113                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
1114            dialect: the dialect used to parse the input expression.
1115            copy: if `False`, modify this expression instance in-place.
1116            opts: other options to use to parse the input expressions.
1117
1118        Returns:
1119            The modified Select expression.
1120        """
1121        return _apply_builder(
1122            expression=expression,
1123            instance=self,
1124            arg="offset",
1125            into=Offset,
1126            prefix="OFFSET",
1127            dialect=dialect,
1128            copy=copy,
1129            into_arg="expression",
1130            **opts,
1131        )
1132
1133    def order_by(
1134        self: Q,
1135        *expressions: t.Optional[ExpOrStr],
1136        append: bool = True,
1137        dialect: DialectType = None,
1138        copy: bool = True,
1139        **opts,
1140    ) -> Q:
1141        """
1142        Set the ORDER BY expression.
1143
1144        Example:
1145            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
1146            'SELECT x FROM tbl ORDER BY x DESC'
1147
1148        Args:
1149            *expressions: the SQL code strings to parse.
1150                If a `Group` instance is passed, this is used as-is.
1151                If another `Expression` instance is passed, it will be wrapped in a `Order`.
1152            append: if `True`, add to any existing expressions.
1153                Otherwise, this flattens all the `Order` expression into a single expression.
1154            dialect: the dialect used to parse the input expression.
1155            copy: if `False`, modify this expression instance in-place.
1156            opts: other options to use to parse the input expressions.
1157
1158        Returns:
1159            The modified Select expression.
1160        """
1161        return _apply_child_list_builder(
1162            *expressions,
1163            instance=self,
1164            arg="order",
1165            append=append,
1166            copy=copy,
1167            prefix="ORDER BY",
1168            into=Order,
1169            dialect=dialect,
1170            **opts,
1171        )
1172
1173    @property
1174    def ctes(self) -> t.List[CTE]:
1175        """Returns a list of all the CTEs attached to this query."""
1176        with_ = self.args.get("with")
1177        return with_.expressions if with_ else []
1178
1179    @property
1180    def selects(self) -> t.List[Expression]:
1181        """Returns the query's projections."""
1182        raise NotImplementedError("Query objects must implement `selects`")
1183
1184    @property
1185    def named_selects(self) -> t.List[str]:
1186        """Returns the output names of the query's projections."""
1187        raise NotImplementedError("Query objects must implement `named_selects`")
1188
1189    def select(
1190        self: Q,
1191        *expressions: t.Optional[ExpOrStr],
1192        append: bool = True,
1193        dialect: DialectType = None,
1194        copy: bool = True,
1195        **opts,
1196    ) -> Q:
1197        """
1198        Append to or set the SELECT expressions.
1199
1200        Example:
1201            >>> Select().select("x", "y").sql()
1202            'SELECT x, y'
1203
1204        Args:
1205            *expressions: the SQL code strings to parse.
1206                If an `Expression` instance is passed, it will be used as-is.
1207            append: if `True`, add to any existing expressions.
1208                Otherwise, this resets the expressions.
1209            dialect: the dialect used to parse the input expressions.
1210            copy: if `False`, modify this expression instance in-place.
1211            opts: other options to use to parse the input expressions.
1212
1213        Returns:
1214            The modified Query expression.
1215        """
1216        raise NotImplementedError("Query objects must implement `select`")
1217
1218    def with_(
1219        self: Q,
1220        alias: ExpOrStr,
1221        as_: ExpOrStr,
1222        recursive: t.Optional[bool] = None,
1223        materialized: t.Optional[bool] = None,
1224        append: bool = True,
1225        dialect: DialectType = None,
1226        copy: bool = True,
1227        scalar: bool = False,
1228        **opts,
1229    ) -> Q:
1230        """
1231        Append to or set the common table expressions.
1232
1233        Example:
1234            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
1235            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
1236
1237        Args:
1238            alias: the SQL code string to parse as the table name.
1239                If an `Expression` instance is passed, this is used as-is.
1240            as_: the SQL code string to parse as the table expression.
1241                If an `Expression` instance is passed, it will be used as-is.
1242            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1243            materialized: set the MATERIALIZED part of the expression.
1244            append: if `True`, add to any existing expressions.
1245                Otherwise, this resets the expressions.
1246            dialect: the dialect used to parse the input expression.
1247            copy: if `False`, modify this expression instance in-place.
1248            scalar: if `True`, this is a scalar common table expression.
1249            opts: other options to use to parse the input expressions.
1250
1251        Returns:
1252            The modified expression.
1253        """
1254        return _apply_cte_builder(
1255            self,
1256            alias,
1257            as_,
1258            recursive=recursive,
1259            materialized=materialized,
1260            append=append,
1261            dialect=dialect,
1262            copy=copy,
1263            scalar=scalar,
1264            **opts,
1265        )
1266
1267    def union(
1268        self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1269    ) -> Union:
1270        """
1271        Builds a UNION expression.
1272
1273        Example:
1274            >>> import sqlglot
1275            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
1276            'SELECT * FROM foo UNION SELECT * FROM bla'
1277
1278        Args:
1279            expressions: the SQL code strings.
1280                If `Expression` instances are passed, they will be used as-is.
1281            distinct: set the DISTINCT flag if and only if this is true.
1282            dialect: the dialect used to parse the input expression.
1283            opts: other options to use to parse the input expressions.
1284
1285        Returns:
1286            The new Union expression.
1287        """
1288        return union(self, *expressions, distinct=distinct, dialect=dialect, **opts)
1289
1290    def intersect(
1291        self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1292    ) -> Intersect:
1293        """
1294        Builds an INTERSECT expression.
1295
1296        Example:
1297            >>> import sqlglot
1298            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
1299            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
1300
1301        Args:
1302            expressions: the SQL code strings.
1303                If `Expression` instances are passed, they will be used as-is.
1304            distinct: set the DISTINCT flag if and only if this is true.
1305            dialect: the dialect used to parse the input expression.
1306            opts: other options to use to parse the input expressions.
1307
1308        Returns:
1309            The new Intersect expression.
1310        """
1311        return intersect(self, *expressions, distinct=distinct, dialect=dialect, **opts)
1312
1313    def except_(
1314        self, *expressions: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
1315    ) -> Except:
1316        """
1317        Builds an EXCEPT expression.
1318
1319        Example:
1320            >>> import sqlglot
1321            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
1322            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
1323
1324        Args:
1325            expressions: the SQL code strings.
1326                If `Expression` instance are passed, they will be used as-is.
1327            distinct: set the DISTINCT flag if and only if this is true.
1328            dialect: the dialect used to parse the input expression.
1329            opts: other options to use to parse the input expressions.
1330
1331        Returns:
1332            The new Except expression.
1333        """
1334        return except_(self, *expressions, distinct=distinct, dialect=dialect, **opts)
1335
1336
1337class UDTF(DerivedTable):
1338    @property
1339    def selects(self) -> t.List[Expression]:
1340        alias = self.args.get("alias")
1341        return alias.columns if alias else []
1342
1343
1344class Cache(Expression):
1345    arg_types = {
1346        "this": True,
1347        "lazy": False,
1348        "options": False,
1349        "expression": False,
1350    }
1351
1352
1353class Uncache(Expression):
1354    arg_types = {"this": True, "exists": False}
1355
1356
1357class Refresh(Expression):
1358    pass
1359
1360
1361class DDL(Expression):
1362    @property
1363    def ctes(self) -> t.List[CTE]:
1364        """Returns a list of all the CTEs attached to this statement."""
1365        with_ = self.args.get("with")
1366        return with_.expressions if with_ else []
1367
1368    @property
1369    def selects(self) -> t.List[Expression]:
1370        """If this statement contains a query (e.g. a CTAS), this returns the query's projections."""
1371        return self.expression.selects if isinstance(self.expression, Query) else []
1372
1373    @property
1374    def named_selects(self) -> t.List[str]:
1375        """
1376        If this statement contains a query (e.g. a CTAS), this returns the output
1377        names of the query's projections.
1378        """
1379        return self.expression.named_selects if isinstance(self.expression, Query) else []
1380
1381
1382class DML(Expression):
1383    def returning(
1384        self,
1385        expression: ExpOrStr,
1386        dialect: DialectType = None,
1387        copy: bool = True,
1388        **opts,
1389    ) -> "Self":
1390        """
1391        Set the RETURNING expression. Not supported by all dialects.
1392
1393        Example:
1394            >>> delete("tbl").returning("*", dialect="postgres").sql()
1395            'DELETE FROM tbl RETURNING *'
1396
1397        Args:
1398            expression: the SQL code strings to parse.
1399                If an `Expression` instance is passed, it will be used as-is.
1400            dialect: the dialect used to parse the input expressions.
1401            copy: if `False`, modify this expression instance in-place.
1402            opts: other options to use to parse the input expressions.
1403
1404        Returns:
1405            Delete: the modified expression.
1406        """
1407        return _apply_builder(
1408            expression=expression,
1409            instance=self,
1410            arg="returning",
1411            prefix="RETURNING",
1412            dialect=dialect,
1413            copy=copy,
1414            into=Returning,
1415            **opts,
1416        )
1417
1418
1419class Create(DDL):
1420    arg_types = {
1421        "with": False,
1422        "this": True,
1423        "kind": True,
1424        "expression": False,
1425        "exists": False,
1426        "properties": False,
1427        "replace": False,
1428        "refresh": False,
1429        "unique": False,
1430        "indexes": False,
1431        "no_schema_binding": False,
1432        "begin": False,
1433        "end": False,
1434        "clone": False,
1435        "concurrently": False,
1436        "clustered": False,
1437    }
1438
1439    @property
1440    def kind(self) -> t.Optional[str]:
1441        kind = self.args.get("kind")
1442        return kind and kind.upper()
1443
1444
1445class SequenceProperties(Expression):
1446    arg_types = {
1447        "increment": False,
1448        "minvalue": False,
1449        "maxvalue": False,
1450        "cache": False,
1451        "start": False,
1452        "owned": False,
1453        "options": False,
1454    }
1455
1456
1457class TruncateTable(Expression):
1458    arg_types = {
1459        "expressions": True,
1460        "is_database": False,
1461        "exists": False,
1462        "only": False,
1463        "cluster": False,
1464        "identity": False,
1465        "option": False,
1466        "partition": False,
1467    }
1468
1469
1470# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1471# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1472# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1473class Clone(Expression):
1474    arg_types = {"this": True, "shallow": False, "copy": False}
1475
1476
1477class Describe(Expression):
1478    arg_types = {
1479        "this": True,
1480        "style": False,
1481        "kind": False,
1482        "expressions": False,
1483        "partition": False,
1484        "format": False,
1485    }
1486
1487
1488# https://duckdb.org/docs/sql/statements/attach.html#attach
1489class Attach(Expression):
1490    arg_types = {"this": True, "exists": False, "expressions": False}
1491
1492
1493# https://duckdb.org/docs/sql/statements/attach.html#detach
1494class Detach(Expression):
1495    arg_types = {"this": True, "exists": False}
1496
1497
1498# https://duckdb.org/docs/guides/meta/summarize.html
1499class Summarize(Expression):
1500    arg_types = {"this": True, "table": False}
1501
1502
1503class Kill(Expression):
1504    arg_types = {"this": True, "kind": False}
1505
1506
1507class Pragma(Expression):
1508    pass
1509
1510
1511class Declare(Expression):
1512    arg_types = {"expressions": True}
1513
1514
1515class DeclareItem(Expression):
1516    arg_types = {"this": True, "kind": True, "default": False}
1517
1518
1519class Set(Expression):
1520    arg_types = {"expressions": False, "unset": False, "tag": False}
1521
1522
1523class Heredoc(Expression):
1524    arg_types = {"this": True, "tag": False}
1525
1526
1527class SetItem(Expression):
1528    arg_types = {
1529        "this": False,
1530        "expressions": False,
1531        "kind": False,
1532        "collate": False,  # MySQL SET NAMES statement
1533        "global": False,
1534    }
1535
1536
1537class Show(Expression):
1538    arg_types = {
1539        "this": True,
1540        "history": False,
1541        "terse": False,
1542        "target": False,
1543        "offset": False,
1544        "starts_with": False,
1545        "limit": False,
1546        "from": False,
1547        "like": False,
1548        "where": False,
1549        "db": False,
1550        "scope": False,
1551        "scope_kind": False,
1552        "full": False,
1553        "mutex": False,
1554        "query": False,
1555        "channel": False,
1556        "global": False,
1557        "log": False,
1558        "position": False,
1559        "types": False,
1560        "privileges": False,
1561    }
1562
1563
1564class UserDefinedFunction(Expression):
1565    arg_types = {"this": True, "expressions": False, "wrapped": False}
1566
1567
1568class CharacterSet(Expression):
1569    arg_types = {"this": True, "default": False}
1570
1571
1572class RecursiveWithSearch(Expression):
1573    arg_types = {"kind": True, "this": True, "expression": True, "using": False}
1574
1575
1576class With(Expression):
1577    arg_types = {"expressions": True, "recursive": False, "search": False}
1578
1579    @property
1580    def recursive(self) -> bool:
1581        return bool(self.args.get("recursive"))
1582
1583
1584class WithinGroup(Expression):
1585    arg_types = {"this": True, "expression": False}
1586
1587
1588# clickhouse supports scalar ctes
1589# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1590class CTE(DerivedTable):
1591    arg_types = {
1592        "this": True,
1593        "alias": True,
1594        "scalar": False,
1595        "materialized": False,
1596    }
1597
1598
1599class ProjectionDef(Expression):
1600    arg_types = {"this": True, "expression": True}
1601
1602
1603class TableAlias(Expression):
1604    arg_types = {"this": False, "columns": False}
1605
1606    @property
1607    def columns(self):
1608        return self.args.get("columns") or []
1609
1610
1611class BitString(Condition):
1612    pass
1613
1614
1615class HexString(Condition):
1616    arg_types = {"this": True, "is_integer": False}
1617
1618
1619class ByteString(Condition):
1620    pass
1621
1622
1623class RawString(Condition):
1624    pass
1625
1626
1627class UnicodeString(Condition):
1628    arg_types = {"this": True, "escape": False}
1629
1630
1631class Column(Condition):
1632    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1633
1634    @property
1635    def table(self) -> str:
1636        return self.text("table")
1637
1638    @property
1639    def db(self) -> str:
1640        return self.text("db")
1641
1642    @property
1643    def catalog(self) -> str:
1644        return self.text("catalog")
1645
1646    @property
1647    def output_name(self) -> str:
1648        return self.name
1649
1650    @property
1651    def parts(self) -> t.List[Identifier]:
1652        """Return the parts of a column in order catalog, db, table, name."""
1653        return [
1654            t.cast(Identifier, self.args[part])
1655            for part in ("catalog", "db", "table", "this")
1656            if self.args.get(part)
1657        ]
1658
1659    def to_dot(self) -> Dot | Identifier:
1660        """Converts the column into a dot expression."""
1661        parts = self.parts
1662        parent = self.parent
1663
1664        while parent:
1665            if isinstance(parent, Dot):
1666                parts.append(parent.expression)
1667            parent = parent.parent
1668
1669        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1670
1671
1672class ColumnPosition(Expression):
1673    arg_types = {"this": False, "position": True}
1674
1675
1676class ColumnDef(Expression):
1677    arg_types = {
1678        "this": True,
1679        "kind": False,
1680        "constraints": False,
1681        "exists": False,
1682        "position": False,
1683        "default": False,
1684        "output": False,
1685    }
1686
1687    @property
1688    def constraints(self) -> t.List[ColumnConstraint]:
1689        return self.args.get("constraints") or []
1690
1691    @property
1692    def kind(self) -> t.Optional[DataType]:
1693        return self.args.get("kind")
1694
1695
1696class AlterColumn(Expression):
1697    arg_types = {
1698        "this": True,
1699        "dtype": False,
1700        "collate": False,
1701        "using": False,
1702        "default": False,
1703        "drop": False,
1704        "comment": False,
1705        "allow_null": False,
1706        "visible": False,
1707    }
1708
1709
1710# https://dev.mysql.com/doc/refman/8.0/en/invisible-indexes.html
1711class AlterIndex(Expression):
1712    arg_types = {"this": True, "visible": True}
1713
1714
1715# https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_TABLE.html
1716class AlterDistStyle(Expression):
1717    pass
1718
1719
1720class AlterSortKey(Expression):
1721    arg_types = {"this": False, "expressions": False, "compound": False}
1722
1723
1724class AlterSet(Expression):
1725    arg_types = {
1726        "expressions": False,
1727        "option": False,
1728        "tablespace": False,
1729        "access_method": False,
1730        "file_format": False,
1731        "copy_options": False,
1732        "tag": False,
1733        "location": False,
1734        "serde": False,
1735    }
1736
1737
1738class RenameColumn(Expression):
1739    arg_types = {"this": True, "to": True, "exists": False}
1740
1741
1742class AlterRename(Expression):
1743    pass
1744
1745
1746class SwapTable(Expression):
1747    pass
1748
1749
1750class Comment(Expression):
1751    arg_types = {
1752        "this": True,
1753        "kind": True,
1754        "expression": True,
1755        "exists": False,
1756        "materialized": False,
1757    }
1758
1759
1760class Comprehension(Expression):
1761    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1762
1763
1764# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1765class MergeTreeTTLAction(Expression):
1766    arg_types = {
1767        "this": True,
1768        "delete": False,
1769        "recompress": False,
1770        "to_disk": False,
1771        "to_volume": False,
1772    }
1773
1774
1775# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1776class MergeTreeTTL(Expression):
1777    arg_types = {
1778        "expressions": True,
1779        "where": False,
1780        "group": False,
1781        "aggregates": False,
1782    }
1783
1784
1785# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1786class IndexConstraintOption(Expression):
1787    arg_types = {
1788        "key_block_size": False,
1789        "using": False,
1790        "parser": False,
1791        "comment": False,
1792        "visible": False,
1793        "engine_attr": False,
1794        "secondary_engine_attr": False,
1795    }
1796
1797
1798class ColumnConstraint(Expression):
1799    arg_types = {"this": False, "kind": True}
1800
1801    @property
1802    def kind(self) -> ColumnConstraintKind:
1803        return self.args["kind"]
1804
1805
1806class ColumnConstraintKind(Expression):
1807    pass
1808
1809
1810class AutoIncrementColumnConstraint(ColumnConstraintKind):
1811    pass
1812
1813
1814class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1815    arg_types = {"this": True, "expression": True}
1816
1817
1818class CaseSpecificColumnConstraint(ColumnConstraintKind):
1819    arg_types = {"not_": True}
1820
1821
1822class CharacterSetColumnConstraint(ColumnConstraintKind):
1823    arg_types = {"this": True}
1824
1825
1826class CheckColumnConstraint(ColumnConstraintKind):
1827    arg_types = {"this": True, "enforced": False}
1828
1829
1830class ClusteredColumnConstraint(ColumnConstraintKind):
1831    pass
1832
1833
1834class CollateColumnConstraint(ColumnConstraintKind):
1835    pass
1836
1837
1838class CommentColumnConstraint(ColumnConstraintKind):
1839    pass
1840
1841
1842class CompressColumnConstraint(ColumnConstraintKind):
1843    arg_types = {"this": False}
1844
1845
1846class DateFormatColumnConstraint(ColumnConstraintKind):
1847    arg_types = {"this": True}
1848
1849
1850class DefaultColumnConstraint(ColumnConstraintKind):
1851    pass
1852
1853
1854class EncodeColumnConstraint(ColumnConstraintKind):
1855    pass
1856
1857
1858# https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE
1859class ExcludeColumnConstraint(ColumnConstraintKind):
1860    pass
1861
1862
1863class EphemeralColumnConstraint(ColumnConstraintKind):
1864    arg_types = {"this": False}
1865
1866
1867class WithOperator(Expression):
1868    arg_types = {"this": True, "op": True}
1869
1870
1871class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1872    # this: True -> ALWAYS, this: False -> BY DEFAULT
1873    arg_types = {
1874        "this": False,
1875        "expression": False,
1876        "on_null": False,
1877        "start": False,
1878        "increment": False,
1879        "minvalue": False,
1880        "maxvalue": False,
1881        "cycle": False,
1882    }
1883
1884
1885class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1886    arg_types = {"start": False, "hidden": False}
1887
1888
1889# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1890# https://github.com/ClickHouse/ClickHouse/blob/master/src/Parsers/ParserCreateQuery.h#L646
1891class IndexColumnConstraint(ColumnConstraintKind):
1892    arg_types = {
1893        "this": False,
1894        "expressions": False,
1895        "kind": False,
1896        "index_type": False,
1897        "options": False,
1898        "expression": False,  # Clickhouse
1899        "granularity": False,
1900    }
1901
1902
1903class InlineLengthColumnConstraint(ColumnConstraintKind):
1904    pass
1905
1906
1907class NonClusteredColumnConstraint(ColumnConstraintKind):
1908    pass
1909
1910
1911class NotForReplicationColumnConstraint(ColumnConstraintKind):
1912    arg_types = {}
1913
1914
1915# https://docs.snowflake.com/en/sql-reference/sql/create-table
1916class MaskingPolicyColumnConstraint(ColumnConstraintKind):
1917    arg_types = {"this": True, "expressions": False}
1918
1919
1920class NotNullColumnConstraint(ColumnConstraintKind):
1921    arg_types = {"allow_null": False}
1922
1923
1924# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1925class OnUpdateColumnConstraint(ColumnConstraintKind):
1926    pass
1927
1928
1929# https://docs.snowflake.com/en/sql-reference/sql/create-external-table#optional-parameters
1930class TransformColumnConstraint(ColumnConstraintKind):
1931    pass
1932
1933
1934class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1935    arg_types = {"desc": False}
1936
1937
1938class TitleColumnConstraint(ColumnConstraintKind):
1939    pass
1940
1941
1942class UniqueColumnConstraint(ColumnConstraintKind):
1943    arg_types = {"this": False, "index_type": False, "on_conflict": False, "nulls": False}
1944
1945
1946class UppercaseColumnConstraint(ColumnConstraintKind):
1947    arg_types: t.Dict[str, t.Any] = {}
1948
1949
1950# https://docs.risingwave.com/processing/watermarks#syntax
1951class WatermarkColumnConstraint(Expression):
1952    arg_types = {"this": True, "expression": True}
1953
1954
1955class PathColumnConstraint(ColumnConstraintKind):
1956    pass
1957
1958
1959# https://docs.snowflake.com/en/sql-reference/sql/create-table
1960class ProjectionPolicyColumnConstraint(ColumnConstraintKind):
1961    pass
1962
1963
1964# computed column expression
1965# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1966class ComputedColumnConstraint(ColumnConstraintKind):
1967    arg_types = {"this": True, "persisted": False, "not_null": False}
1968
1969
1970class Constraint(Expression):
1971    arg_types = {"this": True, "expressions": True}
1972
1973
1974class Delete(DML):
1975    arg_types = {
1976        "with": False,
1977        "this": False,
1978        "using": False,
1979        "where": False,
1980        "returning": False,
1981        "limit": False,
1982        "tables": False,  # Multiple-Table Syntax (MySQL)
1983        "cluster": False,  # Clickhouse
1984    }
1985
1986    def delete(
1987        self,
1988        table: ExpOrStr,
1989        dialect: DialectType = None,
1990        copy: bool = True,
1991        **opts,
1992    ) -> Delete:
1993        """
1994        Create a DELETE expression or replace the table on an existing DELETE expression.
1995
1996        Example:
1997            >>> delete("tbl").sql()
1998            'DELETE FROM tbl'
1999
2000        Args:
2001            table: the table from which to delete.
2002            dialect: the dialect used to parse the input expression.
2003            copy: if `False`, modify this expression instance in-place.
2004            opts: other options to use to parse the input expressions.
2005
2006        Returns:
2007            Delete: the modified expression.
2008        """
2009        return _apply_builder(
2010            expression=table,
2011            instance=self,
2012            arg="this",
2013            dialect=dialect,
2014            into=Table,
2015            copy=copy,
2016            **opts,
2017        )
2018
2019    def where(
2020        self,
2021        *expressions: t.Optional[ExpOrStr],
2022        append: bool = True,
2023        dialect: DialectType = None,
2024        copy: bool = True,
2025        **opts,
2026    ) -> Delete:
2027        """
2028        Append to or set the WHERE expressions.
2029
2030        Example:
2031            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
2032            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
2033
2034        Args:
2035            *expressions: the SQL code strings to parse.
2036                If an `Expression` instance is passed, it will be used as-is.
2037                Multiple expressions are combined with an AND operator.
2038            append: if `True`, AND the new expressions to any existing expression.
2039                Otherwise, this resets the expression.
2040            dialect: the dialect used to parse the input expressions.
2041            copy: if `False`, modify this expression instance in-place.
2042            opts: other options to use to parse the input expressions.
2043
2044        Returns:
2045            Delete: the modified expression.
2046        """
2047        return _apply_conjunction_builder(
2048            *expressions,
2049            instance=self,
2050            arg="where",
2051            append=append,
2052            into=Where,
2053            dialect=dialect,
2054            copy=copy,
2055            **opts,
2056        )
2057
2058
2059class Drop(Expression):
2060    arg_types = {
2061        "this": False,
2062        "kind": False,
2063        "expressions": False,
2064        "exists": False,
2065        "temporary": False,
2066        "materialized": False,
2067        "cascade": False,
2068        "constraints": False,
2069        "purge": False,
2070        "cluster": False,
2071        "concurrently": False,
2072    }
2073
2074    @property
2075    def kind(self) -> t.Optional[str]:
2076        kind = self.args.get("kind")
2077        return kind and kind.upper()
2078
2079
2080# https://cloud.google.com/bigquery/docs/reference/standard-sql/export-statements
2081class Export(Expression):
2082    arg_types = {"this": True, "connection": False, "options": True}
2083
2084
2085class Filter(Expression):
2086    arg_types = {"this": True, "expression": True}
2087
2088
2089class Check(Expression):
2090    pass
2091
2092
2093class Changes(Expression):
2094    arg_types = {"information": True, "at_before": False, "end": False}
2095
2096
2097# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
2098class Connect(Expression):
2099    arg_types = {"start": False, "connect": True, "nocycle": False}
2100
2101
2102class CopyParameter(Expression):
2103    arg_types = {"this": True, "expression": False, "expressions": False}
2104
2105
2106class Copy(DML):
2107    arg_types = {
2108        "this": True,
2109        "kind": True,
2110        "files": True,
2111        "credentials": False,
2112        "format": False,
2113        "params": False,
2114    }
2115
2116
2117class Credentials(Expression):
2118    arg_types = {
2119        "credentials": False,
2120        "encryption": False,
2121        "storage": False,
2122        "iam_role": False,
2123        "region": False,
2124    }
2125
2126
2127class Prior(Expression):
2128    pass
2129
2130
2131class Directory(Expression):
2132    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
2133    arg_types = {"this": True, "local": False, "row_format": False}
2134
2135
2136class ForeignKey(Expression):
2137    arg_types = {
2138        "expressions": False,
2139        "reference": False,
2140        "delete": False,
2141        "update": False,
2142    }
2143
2144
2145class ColumnPrefix(Expression):
2146    arg_types = {"this": True, "expression": True}
2147
2148
2149class PrimaryKey(Expression):
2150    arg_types = {"expressions": True, "options": False}
2151
2152
2153# https://www.postgresql.org/docs/9.1/sql-selectinto.html
2154# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
2155class Into(Expression):
2156    arg_types = {
2157        "this": False,
2158        "temporary": False,
2159        "unlogged": False,
2160        "bulk_collect": False,
2161        "expressions": False,
2162    }
2163
2164
2165class From(Expression):
2166    @property
2167    def name(self) -> str:
2168        return self.this.name
2169
2170    @property
2171    def alias_or_name(self) -> str:
2172        return self.this.alias_or_name
2173
2174
2175class Having(Expression):
2176    pass
2177
2178
2179class Hint(Expression):
2180    arg_types = {"expressions": True}
2181
2182
2183class JoinHint(Expression):
2184    arg_types = {"this": True, "expressions": True}
2185
2186
2187class Identifier(Expression):
2188    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
2189
2190    @property
2191    def quoted(self) -> bool:
2192        return bool(self.args.get("quoted"))
2193
2194    @property
2195    def hashable_args(self) -> t.Any:
2196        return (self.this, self.quoted)
2197
2198    @property
2199    def output_name(self) -> str:
2200        return self.name
2201
2202
2203# https://www.postgresql.org/docs/current/indexes-opclass.html
2204class Opclass(Expression):
2205    arg_types = {"this": True, "expression": True}
2206
2207
2208class Index(Expression):
2209    arg_types = {
2210        "this": False,
2211        "table": False,
2212        "unique": False,
2213        "primary": False,
2214        "amp": False,  # teradata
2215        "params": False,
2216    }
2217
2218
2219class IndexParameters(Expression):
2220    arg_types = {
2221        "using": False,
2222        "include": False,
2223        "columns": False,
2224        "with_storage": False,
2225        "partition_by": False,
2226        "tablespace": False,
2227        "where": False,
2228        "on": False,
2229    }
2230
2231
2232class Insert(DDL, DML):
2233    arg_types = {
2234        "hint": False,
2235        "with": False,
2236        "is_function": False,
2237        "this": False,
2238        "expression": False,
2239        "conflict": False,
2240        "returning": False,
2241        "overwrite": False,
2242        "exists": False,
2243        "alternative": False,
2244        "where": False,
2245        "ignore": False,
2246        "by_name": False,
2247        "stored": False,
2248        "partition": False,
2249        "settings": False,
2250        "source": False,
2251    }
2252
2253    def with_(
2254        self,
2255        alias: ExpOrStr,
2256        as_: ExpOrStr,
2257        recursive: t.Optional[bool] = None,
2258        materialized: t.Optional[bool] = None,
2259        append: bool = True,
2260        dialect: DialectType = None,
2261        copy: bool = True,
2262        **opts,
2263    ) -> Insert:
2264        """
2265        Append to or set the common table expressions.
2266
2267        Example:
2268            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
2269            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
2270
2271        Args:
2272            alias: the SQL code string to parse as the table name.
2273                If an `Expression` instance is passed, this is used as-is.
2274            as_: the SQL code string to parse as the table expression.
2275                If an `Expression` instance is passed, it will be used as-is.
2276            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2277            materialized: set the MATERIALIZED part of the expression.
2278            append: if `True`, add to any existing expressions.
2279                Otherwise, this resets the expressions.
2280            dialect: the dialect used to parse the input expression.
2281            copy: if `False`, modify this expression instance in-place.
2282            opts: other options to use to parse the input expressions.
2283
2284        Returns:
2285            The modified expression.
2286        """
2287        return _apply_cte_builder(
2288            self,
2289            alias,
2290            as_,
2291            recursive=recursive,
2292            materialized=materialized,
2293            append=append,
2294            dialect=dialect,
2295            copy=copy,
2296            **opts,
2297        )
2298
2299
2300class ConditionalInsert(Expression):
2301    arg_types = {"this": True, "expression": False, "else_": False}
2302
2303
2304class MultitableInserts(Expression):
2305    arg_types = {"expressions": True, "kind": True, "source": True}
2306
2307
2308class OnConflict(Expression):
2309    arg_types = {
2310        "duplicate": False,
2311        "expressions": False,
2312        "action": False,
2313        "conflict_keys": False,
2314        "constraint": False,
2315        "where": False,
2316    }
2317
2318
2319class OnCondition(Expression):
2320    arg_types = {"error": False, "empty": False, "null": False}
2321
2322
2323class Returning(Expression):
2324    arg_types = {"expressions": True, "into": False}
2325
2326
2327# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
2328class Introducer(Expression):
2329    arg_types = {"this": True, "expression": True}
2330
2331
2332# national char, like n'utf8'
2333class National(Expression):
2334    pass
2335
2336
2337class LoadData(Expression):
2338    arg_types = {
2339        "this": True,
2340        "local": False,
2341        "overwrite": False,
2342        "inpath": True,
2343        "partition": False,
2344        "input_format": False,
2345        "serde": False,
2346    }
2347
2348
2349class Partition(Expression):
2350    arg_types = {"expressions": True, "subpartition": False}
2351
2352
2353class PartitionRange(Expression):
2354    arg_types = {"this": True, "expression": True}
2355
2356
2357# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#how-to-set-partition-expression
2358class PartitionId(Expression):
2359    pass
2360
2361
2362class Fetch(Expression):
2363    arg_types = {
2364        "direction": False,
2365        "count": False,
2366        "limit_options": False,
2367    }
2368
2369
2370class Grant(Expression):
2371    arg_types = {
2372        "privileges": True,
2373        "kind": False,
2374        "securable": True,
2375        "principals": True,
2376        "grant_option": False,
2377    }
2378
2379
2380class Group(Expression):
2381    arg_types = {
2382        "expressions": False,
2383        "grouping_sets": False,
2384        "cube": False,
2385        "rollup": False,
2386        "totals": False,
2387        "all": False,
2388    }
2389
2390
2391class Cube(Expression):
2392    arg_types = {"expressions": False}
2393
2394
2395class Rollup(Expression):
2396    arg_types = {"expressions": False}
2397
2398
2399class GroupingSets(Expression):
2400    arg_types = {"expressions": True}
2401
2402
2403class Lambda(Expression):
2404    arg_types = {"this": True, "expressions": True}
2405
2406
2407class Limit(Expression):
2408    arg_types = {
2409        "this": False,
2410        "expression": True,
2411        "offset": False,
2412        "limit_options": False,
2413        "expressions": False,
2414    }
2415
2416
2417class LimitOptions(Expression):
2418    arg_types = {
2419        "percent": False,
2420        "rows": False,
2421        "with_ties": False,
2422    }
2423
2424
2425class Literal(Condition):
2426    arg_types = {"this": True, "is_string": True}
2427
2428    @property
2429    def hashable_args(self) -> t.Any:
2430        return (self.this, self.args.get("is_string"))
2431
2432    @classmethod
2433    def number(cls, number) -> Literal:
2434        return cls(this=str(number), is_string=False)
2435
2436    @classmethod
2437    def string(cls, string) -> Literal:
2438        return cls(this=str(string), is_string=True)
2439
2440    @property
2441    def output_name(self) -> str:
2442        return self.name
2443
2444    def to_py(self) -> int | str | Decimal:
2445        if self.is_number:
2446            try:
2447                return int(self.this)
2448            except ValueError:
2449                return Decimal(self.this)
2450        return self.this
2451
2452
2453class Join(Expression):
2454    arg_types = {
2455        "this": True,
2456        "on": False,
2457        "side": False,
2458        "kind": False,
2459        "using": False,
2460        "method": False,
2461        "global": False,
2462        "hint": False,
2463        "match_condition": False,  # Snowflake
2464        "expressions": False,
2465    }
2466
2467    @property
2468    def method(self) -> str:
2469        return self.text("method").upper()
2470
2471    @property
2472    def kind(self) -> str:
2473        return self.text("kind").upper()
2474
2475    @property
2476    def side(self) -> str:
2477        return self.text("side").upper()
2478
2479    @property
2480    def hint(self) -> str:
2481        return self.text("hint").upper()
2482
2483    @property
2484    def alias_or_name(self) -> str:
2485        return self.this.alias_or_name
2486
2487    @property
2488    def is_semi_or_anti_join(self) -> bool:
2489        return self.kind in ("SEMI", "ANTI")
2490
2491    def on(
2492        self,
2493        *expressions: t.Optional[ExpOrStr],
2494        append: bool = True,
2495        dialect: DialectType = None,
2496        copy: bool = True,
2497        **opts,
2498    ) -> Join:
2499        """
2500        Append to or set the ON expressions.
2501
2502        Example:
2503            >>> import sqlglot
2504            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
2505            'JOIN x ON y = 1'
2506
2507        Args:
2508            *expressions: the SQL code strings to parse.
2509                If an `Expression` instance is passed, it will be used as-is.
2510                Multiple expressions are combined with an AND operator.
2511            append: if `True`, AND the new expressions to any existing expression.
2512                Otherwise, this resets the expression.
2513            dialect: the dialect used to parse the input expressions.
2514            copy: if `False`, modify this expression instance in-place.
2515            opts: other options to use to parse the input expressions.
2516
2517        Returns:
2518            The modified Join expression.
2519        """
2520        join = _apply_conjunction_builder(
2521            *expressions,
2522            instance=self,
2523            arg="on",
2524            append=append,
2525            dialect=dialect,
2526            copy=copy,
2527            **opts,
2528        )
2529
2530        if join.kind == "CROSS":
2531            join.set("kind", None)
2532
2533        return join
2534
2535    def using(
2536        self,
2537        *expressions: t.Optional[ExpOrStr],
2538        append: bool = True,
2539        dialect: DialectType = None,
2540        copy: bool = True,
2541        **opts,
2542    ) -> Join:
2543        """
2544        Append to or set the USING expressions.
2545
2546        Example:
2547            >>> import sqlglot
2548            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
2549            'JOIN x USING (foo, bla)'
2550
2551        Args:
2552            *expressions: the SQL code strings to parse.
2553                If an `Expression` instance is passed, it will be used as-is.
2554            append: if `True`, concatenate the new expressions to the existing "using" list.
2555                Otherwise, this resets the expression.
2556            dialect: the dialect used to parse the input expressions.
2557            copy: if `False`, modify this expression instance in-place.
2558            opts: other options to use to parse the input expressions.
2559
2560        Returns:
2561            The modified Join expression.
2562        """
2563        join = _apply_list_builder(
2564            *expressions,
2565            instance=self,
2566            arg="using",
2567            append=append,
2568            dialect=dialect,
2569            copy=copy,
2570            **opts,
2571        )
2572
2573        if join.kind == "CROSS":
2574            join.set("kind", None)
2575
2576        return join
2577
2578
2579class Lateral(UDTF):
2580    arg_types = {
2581        "this": True,
2582        "view": False,
2583        "outer": False,
2584        "alias": False,
2585        "cross_apply": False,  # True -> CROSS APPLY, False -> OUTER APPLY
2586    }
2587
2588
2589class MatchRecognizeMeasure(Expression):
2590    arg_types = {
2591        "this": True,
2592        "window_frame": False,
2593    }
2594
2595
2596class MatchRecognize(Expression):
2597    arg_types = {
2598        "partition_by": False,
2599        "order": False,
2600        "measures": False,
2601        "rows": False,
2602        "after": False,
2603        "pattern": False,
2604        "define": False,
2605        "alias": False,
2606    }
2607
2608
2609# Clickhouse FROM FINAL modifier
2610# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
2611class Final(Expression):
2612    pass
2613
2614
2615class Offset(Expression):
2616    arg_types = {"this": False, "expression": True, "expressions": False}
2617
2618
2619class Order(Expression):
2620    arg_types = {"this": False, "expressions": True, "siblings": False}
2621
2622
2623# https://clickhouse.com/docs/en/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier
2624class WithFill(Expression):
2625    arg_types = {
2626        "from": False,
2627        "to": False,
2628        "step": False,
2629        "interpolate": False,
2630    }
2631
2632
2633# hive specific sorts
2634# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
2635class Cluster(Order):
2636    pass
2637
2638
2639class Distribute(Order):
2640    pass
2641
2642
2643class Sort(Order):
2644    pass
2645
2646
2647class Ordered(Expression):
2648    arg_types = {"this": True, "desc": False, "nulls_first": True, "with_fill": False}
2649
2650
2651class Property(Expression):
2652    arg_types = {"this": True, "value": True}
2653
2654
2655class GrantPrivilege(Expression):
2656    arg_types = {"this": True, "expressions": False}
2657
2658
2659class GrantPrincipal(Expression):
2660    arg_types = {"this": True, "kind": False}
2661
2662
2663class AllowedValuesProperty(Expression):
2664    arg_types = {"expressions": True}
2665
2666
2667class AlgorithmProperty(Property):
2668    arg_types = {"this": True}
2669
2670
2671class AutoIncrementProperty(Property):
2672    arg_types = {"this": True}
2673
2674
2675# https://docs.aws.amazon.com/prescriptive-guidance/latest/materialized-views-redshift/refreshing-materialized-views.html
2676class AutoRefreshProperty(Property):
2677    arg_types = {"this": True}
2678
2679
2680class BackupProperty(Property):
2681    arg_types = {"this": True}
2682
2683
2684class BlockCompressionProperty(Property):
2685    arg_types = {
2686        "autotemp": False,
2687        "always": False,
2688        "default": False,
2689        "manual": False,
2690        "never": False,
2691    }
2692
2693
2694class CharacterSetProperty(Property):
2695    arg_types = {"this": True, "default": True}
2696
2697
2698class ChecksumProperty(Property):
2699    arg_types = {"on": False, "default": False}
2700
2701
2702class CollateProperty(Property):
2703    arg_types = {"this": True, "default": False}
2704
2705
2706class CopyGrantsProperty(Property):
2707    arg_types = {}
2708
2709
2710class DataBlocksizeProperty(Property):
2711    arg_types = {
2712        "size": False,
2713        "units": False,
2714        "minimum": False,
2715        "maximum": False,
2716        "default": False,
2717    }
2718
2719
2720class DataDeletionProperty(Property):
2721    arg_types = {"on": True, "filter_col": False, "retention_period": False}
2722
2723
2724class DefinerProperty(Property):
2725    arg_types = {"this": True}
2726
2727
2728class DistKeyProperty(Property):
2729    arg_types = {"this": True}
2730
2731
2732# https://docs.starrocks.io/docs/sql-reference/sql-statements/data-definition/CREATE_TABLE/#distribution_desc
2733# https://doris.apache.org/docs/sql-manual/sql-statements/Data-Definition-Statements/Create/CREATE-TABLE?_highlight=create&_highlight=table#distribution_desc
2734class DistributedByProperty(Property):
2735    arg_types = {"expressions": False, "kind": True, "buckets": False, "order": False}
2736
2737
2738class DistStyleProperty(Property):
2739    arg_types = {"this": True}
2740
2741
2742class DuplicateKeyProperty(Property):
2743    arg_types = {"expressions": True}
2744
2745
2746class EngineProperty(Property):
2747    arg_types = {"this": True}
2748
2749
2750class HeapProperty(Property):
2751    arg_types = {}
2752
2753
2754class ToTableProperty(Property):
2755    arg_types = {"this": True}
2756
2757
2758class ExecuteAsProperty(Property):
2759    arg_types = {"this": True}
2760
2761
2762class ExternalProperty(Property):
2763    arg_types = {"this": False}
2764
2765
2766class FallbackProperty(Property):
2767    arg_types = {"no": True, "protection": False}
2768
2769
2770class FileFormatProperty(Property):
2771    arg_types = {"this": True}
2772
2773
2774class FreespaceProperty(Property):
2775    arg_types = {"this": True, "percent": False}
2776
2777
2778class GlobalProperty(Property):
2779    arg_types = {}
2780
2781
2782class IcebergProperty(Property):
2783    arg_types = {}
2784
2785
2786class InheritsProperty(Property):
2787    arg_types = {"expressions": True}
2788
2789
2790class InputModelProperty(Property):
2791    arg_types = {"this": True}
2792
2793
2794class OutputModelProperty(Property):
2795    arg_types = {"this": True}
2796
2797
2798class IsolatedLoadingProperty(Property):
2799    arg_types = {"no": False, "concurrent": False, "target": False}
2800
2801
2802class JournalProperty(Property):
2803    arg_types = {
2804        "no": False,
2805        "dual": False,
2806        "before": False,
2807        "local": False,
2808        "after": False,
2809    }
2810
2811
2812class LanguageProperty(Property):
2813    arg_types = {"this": True}
2814
2815
2816# spark ddl
2817class ClusteredByProperty(Property):
2818    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2819
2820
2821class DictProperty(Property):
2822    arg_types = {"this": True, "kind": True, "settings": False}
2823
2824
2825class DictSubProperty(Property):
2826    pass
2827
2828
2829class DictRange(Property):
2830    arg_types = {"this": True, "min": True, "max": True}
2831
2832
2833class DynamicProperty(Property):
2834    arg_types = {}
2835
2836
2837# Clickhouse CREATE ... ON CLUSTER modifier
2838# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2839class OnCluster(Property):
2840    arg_types = {"this": True}
2841
2842
2843# Clickhouse EMPTY table "property"
2844class EmptyProperty(Property):
2845    arg_types = {}
2846
2847
2848class LikeProperty(Property):
2849    arg_types = {"this": True, "expressions": False}
2850
2851
2852class LocationProperty(Property):
2853    arg_types = {"this": True}
2854
2855
2856class LockProperty(Property):
2857    arg_types = {"this": True}
2858
2859
2860class LockingProperty(Property):
2861    arg_types = {
2862        "this": False,
2863        "kind": True,
2864        "for_or_in": False,
2865        "lock_type": True,
2866        "override": False,
2867    }
2868
2869
2870class LogProperty(Property):
2871    arg_types = {"no": True}
2872
2873
2874class MaterializedProperty(Property):
2875    arg_types = {"this": False}
2876
2877
2878class MergeBlockRatioProperty(Property):
2879    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2880
2881
2882class NoPrimaryIndexProperty(Property):
2883    arg_types = {}
2884
2885
2886class OnProperty(Property):
2887    arg_types = {"this": True}
2888
2889
2890class OnCommitProperty(Property):
2891    arg_types = {"delete": False}
2892
2893
2894class PartitionedByProperty(Property):
2895    arg_types = {"this": True}
2896
2897
2898# https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
2899class PartitionByRangeProperty(Property):
2900    arg_types = {"partition_expressions": True, "create_expressions": True}
2901
2902
2903# https://docs.starrocks.io/docs/table_design/data_distribution/#range-partitioning
2904class PartitionByRangePropertyDynamic(Expression):
2905    arg_types = {"this": False, "start": True, "end": True, "every": True}
2906
2907
2908# https://docs.starrocks.io/docs/sql-reference/sql-statements/table_bucket_part_index/CREATE_TABLE/
2909class UniqueKeyProperty(Property):
2910    arg_types = {"expressions": True}
2911
2912
2913# https://www.postgresql.org/docs/current/sql-createtable.html
2914class PartitionBoundSpec(Expression):
2915    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2916    arg_types = {
2917        "this": False,
2918        "expression": False,
2919        "from_expressions": False,
2920        "to_expressions": False,
2921    }
2922
2923
2924class PartitionedOfProperty(Property):
2925    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2926    arg_types = {"this": True, "expression": True}
2927
2928
2929class StreamingTableProperty(Property):
2930    arg_types = {}
2931
2932
2933class RemoteWithConnectionModelProperty(Property):
2934    arg_types = {"this": True}
2935
2936
2937class ReturnsProperty(Property):
2938    arg_types = {"this": False, "is_table": False, "table": False, "null": False}
2939
2940
2941class StrictProperty(Property):
2942    arg_types = {}
2943
2944
2945class RowFormatProperty(Property):
2946    arg_types = {"this": True}
2947
2948
2949class RowFormatDelimitedProperty(Property):
2950    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2951    arg_types = {
2952        "fields": False,
2953        "escaped": False,
2954        "collection_items": False,
2955        "map_keys": False,
2956        "lines": False,
2957        "null": False,
2958        "serde": False,
2959    }
2960
2961
2962class RowFormatSerdeProperty(Property):
2963    arg_types = {"this": True, "serde_properties": False}
2964
2965
2966# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2967class QueryTransform(Expression):
2968    arg_types = {
2969        "expressions": True,
2970        "command_script": True,
2971        "schema": False,
2972        "row_format_before": False,
2973        "record_writer": False,
2974        "row_format_after": False,
2975        "record_reader": False,
2976    }
2977
2978
2979class SampleProperty(Property):
2980    arg_types = {"this": True}
2981
2982
2983# https://prestodb.io/docs/current/sql/create-view.html#synopsis
2984class SecurityProperty(Property):
2985    arg_types = {"this": True}
2986
2987
2988class SchemaCommentProperty(Property):
2989    arg_types = {"this": True}
2990
2991
2992class SerdeProperties(Property):
2993    arg_types = {"expressions": True, "with": False}
2994
2995
2996class SetProperty(Property):
2997    arg_types = {"multi": True}
2998
2999
3000class SharingProperty(Property):
3001    arg_types = {"this": False}
3002
3003
3004class SetConfigProperty(Property):
3005    arg_types = {"this": True}
3006
3007
3008class SettingsProperty(Property):
3009    arg_types = {"expressions": True}
3010
3011
3012class SortKeyProperty(Property):
3013    arg_types = {"this": True, "compound": False}
3014
3015
3016class SqlReadWriteProperty(Property):
3017    arg_types = {"this": True}
3018
3019
3020class SqlSecurityProperty(Property):
3021    arg_types = {"definer": True}
3022
3023
3024class StabilityProperty(Property):
3025    arg_types = {"this": True}
3026
3027
3028class StorageHandlerProperty(Property):
3029    arg_types = {"this": True}
3030
3031
3032class TemporaryProperty(Property):
3033    arg_types = {"this": False}
3034
3035
3036class SecureProperty(Property):
3037    arg_types = {}
3038
3039
3040# https://docs.snowflake.com/en/sql-reference/sql/create-table
3041class Tags(ColumnConstraintKind, Property):
3042    arg_types = {"expressions": True}
3043
3044
3045class TransformModelProperty(Property):
3046    arg_types = {"expressions": True}
3047
3048
3049class TransientProperty(Property):
3050    arg_types = {"this": False}
3051
3052
3053class UnloggedProperty(Property):
3054    arg_types = {}
3055
3056
3057# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16
3058class ViewAttributeProperty(Property):
3059    arg_types = {"this": True}
3060
3061
3062class VolatileProperty(Property):
3063    arg_types = {"this": False}
3064
3065
3066class WithDataProperty(Property):
3067    arg_types = {"no": True, "statistics": False}
3068
3069
3070class WithJournalTableProperty(Property):
3071    arg_types = {"this": True}
3072
3073
3074class WithSchemaBindingProperty(Property):
3075    arg_types = {"this": True}
3076
3077
3078class WithSystemVersioningProperty(Property):
3079    arg_types = {
3080        "on": False,
3081        "this": False,
3082        "data_consistency": False,
3083        "retention_period": False,
3084        "with": True,
3085    }
3086
3087
3088class WithProcedureOptions(Property):
3089    arg_types = {"expressions": True}
3090
3091
3092class EncodeProperty(Property):
3093    arg_types = {"this": True, "properties": False, "key": False}
3094
3095
3096class IncludeProperty(Property):
3097    arg_types = {"this": True, "alias": False, "column_def": False}
3098
3099
3100class ForceProperty(Property):
3101    arg_types = {}
3102
3103
3104class Properties(Expression):
3105    arg_types = {"expressions": True}
3106
3107    NAME_TO_PROPERTY = {
3108        "ALGORITHM": AlgorithmProperty,
3109        "AUTO_INCREMENT": AutoIncrementProperty,
3110        "CHARACTER SET": CharacterSetProperty,
3111        "CLUSTERED_BY": ClusteredByProperty,
3112        "COLLATE": CollateProperty,
3113        "COMMENT": SchemaCommentProperty,
3114        "DEFINER": DefinerProperty,
3115        "DISTKEY": DistKeyProperty,
3116        "DISTRIBUTED_BY": DistributedByProperty,
3117        "DISTSTYLE": DistStyleProperty,
3118        "ENGINE": EngineProperty,
3119        "EXECUTE AS": ExecuteAsProperty,
3120        "FORMAT": FileFormatProperty,
3121        "LANGUAGE": LanguageProperty,
3122        "LOCATION": LocationProperty,
3123        "LOCK": LockProperty,
3124        "PARTITIONED_BY": PartitionedByProperty,
3125        "RETURNS": ReturnsProperty,
3126        "ROW_FORMAT": RowFormatProperty,
3127        "SORTKEY": SortKeyProperty,
3128        "ENCODE": EncodeProperty,
3129        "INCLUDE": IncludeProperty,
3130    }
3131
3132    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
3133
3134    # CREATE property locations
3135    # Form: schema specified
3136    #   create [POST_CREATE]
3137    #     table a [POST_NAME]
3138    #     (b int) [POST_SCHEMA]
3139    #     with ([POST_WITH])
3140    #     index (b) [POST_INDEX]
3141    #
3142    # Form: alias selection
3143    #   create [POST_CREATE]
3144    #     table a [POST_NAME]
3145    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
3146    #     index (c) [POST_INDEX]
3147    class Location(AutoName):
3148        POST_CREATE = auto()
3149        POST_NAME = auto()
3150        POST_SCHEMA = auto()
3151        POST_WITH = auto()
3152        POST_ALIAS = auto()
3153        POST_EXPRESSION = auto()
3154        POST_INDEX = auto()
3155        UNSUPPORTED = auto()
3156
3157    @classmethod
3158    def from_dict(cls, properties_dict: t.Dict) -> Properties:
3159        expressions = []
3160        for key, value in properties_dict.items():
3161            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
3162            if property_cls:
3163                expressions.append(property_cls(this=convert(value)))
3164            else:
3165                expressions.append(Property(this=Literal.string(key), value=convert(value)))
3166
3167        return cls(expressions=expressions)
3168
3169
3170class Qualify(Expression):
3171    pass
3172
3173
3174class InputOutputFormat(Expression):
3175    arg_types = {"input_format": False, "output_format": False}
3176
3177
3178# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
3179class Return(Expression):
3180    pass
3181
3182
3183class Reference(Expression):
3184    arg_types = {"this": True, "expressions": False, "options": False}
3185
3186
3187class Tuple(Expression):
3188    arg_types = {"expressions": False}
3189
3190    def isin(
3191        self,
3192        *expressions: t.Any,
3193        query: t.Optional[ExpOrStr] = None,
3194        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
3195        copy: bool = True,
3196        **opts,
3197    ) -> In:
3198        return In(
3199            this=maybe_copy(self, copy),
3200            expressions=[convert(e, copy=copy) for e in expressions],
3201            query=maybe_parse(query, copy=copy, **opts) if query else None,
3202            unnest=(
3203                Unnest(
3204                    expressions=[
3205                        maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts)
3206                        for e in ensure_list(unnest)
3207                    ]
3208                )
3209                if unnest
3210                else None
3211            ),
3212        )
3213
3214
3215QUERY_MODIFIERS = {
3216    "match": False,
3217    "laterals": False,
3218    "joins": False,
3219    "connect": False,
3220    "pivots": False,
3221    "prewhere": False,
3222    "where": False,
3223    "group": False,
3224    "having": False,
3225    "qualify": False,
3226    "windows": False,
3227    "distribute": False,
3228    "sort": False,
3229    "cluster": False,
3230    "order": False,
3231    "limit": False,
3232    "offset": False,
3233    "locks": False,
3234    "sample": False,
3235    "settings": False,
3236    "format": False,
3237    "options": False,
3238}
3239
3240
3241# https://learn.microsoft.com/en-us/sql/t-sql/queries/option-clause-transact-sql?view=sql-server-ver16
3242# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query?view=sql-server-ver16
3243class QueryOption(Expression):
3244    arg_types = {"this": True, "expression": False}
3245
3246
3247# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
3248class WithTableHint(Expression):
3249    arg_types = {"expressions": True}
3250
3251
3252# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
3253class IndexTableHint(Expression):
3254    arg_types = {"this": True, "expressions": False, "target": False}
3255
3256
3257# https://docs.snowflake.com/en/sql-reference/constructs/at-before
3258class HistoricalData(Expression):
3259    arg_types = {"this": True, "kind": True, "expression": True}
3260
3261
3262# https://docs.snowflake.com/en/sql-reference/sql/put
3263class Put(Expression):
3264    arg_types = {"this": True, "target": True, "properties": False}
3265
3266
3267class Table(Expression):
3268    arg_types = {
3269        "this": False,
3270        "alias": False,
3271        "db": False,
3272        "catalog": False,
3273        "laterals": False,
3274        "joins": False,
3275        "pivots": False,
3276        "hints": False,
3277        "system_time": False,
3278        "version": False,
3279        "format": False,
3280        "pattern": False,
3281        "ordinality": False,
3282        "when": False,
3283        "only": False,
3284        "partition": False,
3285        "changes": False,
3286        "rows_from": False,
3287        "sample": False,
3288    }
3289
3290    @property
3291    def name(self) -> str:
3292        if not self.this or isinstance(self.this, Func):
3293            return ""
3294        return self.this.name
3295
3296    @property
3297    def db(self) -> str:
3298        return self.text("db")
3299
3300    @property
3301    def catalog(self) -> str:
3302        return self.text("catalog")
3303
3304    @property
3305    def selects(self) -> t.List[Expression]:
3306        return []
3307
3308    @property
3309    def named_selects(self) -> t.List[str]:
3310        return []
3311
3312    @property
3313    def parts(self) -> t.List[Expression]:
3314        """Return the parts of a table in order catalog, db, table."""
3315        parts: t.List[Expression] = []
3316
3317        for arg in ("catalog", "db", "this"):
3318            part = self.args.get(arg)
3319
3320            if isinstance(part, Dot):
3321                parts.extend(part.flatten())
3322            elif isinstance(part, Expression):
3323                parts.append(part)
3324
3325        return parts
3326
3327    def to_column(self, copy: bool = True) -> Expression:
3328        parts = self.parts
3329        last_part = parts[-1]
3330
3331        if isinstance(last_part, Identifier):
3332            col: Expression = column(*reversed(parts[0:4]), fields=parts[4:], copy=copy)  # type: ignore
3333        else:
3334            # This branch will be reached if a function or array is wrapped in a `Table`
3335            col = last_part
3336
3337        alias = self.args.get("alias")
3338        if alias:
3339            col = alias_(col, alias.this, copy=copy)
3340
3341        return col
3342
3343
3344class SetOperation(Query):
3345    arg_types = {
3346        "with": False,
3347        "this": True,
3348        "expression": True,
3349        "distinct": False,
3350        "by_name": False,
3351        **QUERY_MODIFIERS,
3352    }
3353
3354    def select(
3355        self: S,
3356        *expressions: t.Optional[ExpOrStr],
3357        append: bool = True,
3358        dialect: DialectType = None,
3359        copy: bool = True,
3360        **opts,
3361    ) -> S:
3362        this = maybe_copy(self, copy)
3363        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
3364        this.expression.unnest().select(
3365            *expressions, append=append, dialect=dialect, copy=False, **opts
3366        )
3367        return this
3368
3369    @property
3370    def named_selects(self) -> t.List[str]:
3371        return self.this.unnest().named_selects
3372
3373    @property
3374    def is_star(self) -> bool:
3375        return self.this.is_star or self.expression.is_star
3376
3377    @property
3378    def selects(self) -> t.List[Expression]:
3379        return self.this.unnest().selects
3380
3381    @property
3382    def left(self) -> Query:
3383        return self.this
3384
3385    @property
3386    def right(self) -> Query:
3387        return self.expression
3388
3389
3390class Union(SetOperation):
3391    pass
3392
3393
3394class Except(SetOperation):
3395    pass
3396
3397
3398class Intersect(SetOperation):
3399    pass
3400
3401
3402class Update(DML):
3403    arg_types = {
3404        "with": False,
3405        "this": False,
3406        "expressions": True,
3407        "from": False,
3408        "where": False,
3409        "returning": False,
3410        "order": False,
3411        "limit": False,
3412    }
3413
3414    def table(
3415        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3416    ) -> Update:
3417        """
3418        Set the table to update.
3419
3420        Example:
3421            >>> Update().table("my_table").set_("x = 1").sql()
3422            'UPDATE my_table SET x = 1'
3423
3424        Args:
3425            expression : the SQL code strings to parse.
3426                If a `Table` instance is passed, this is used as-is.
3427                If another `Expression` instance is passed, it will be wrapped in a `Table`.
3428            dialect: the dialect used to parse the input expression.
3429            copy: if `False`, modify this expression instance in-place.
3430            opts: other options to use to parse the input expressions.
3431
3432        Returns:
3433            The modified Update expression.
3434        """
3435        return _apply_builder(
3436            expression=expression,
3437            instance=self,
3438            arg="this",
3439            into=Table,
3440            prefix=None,
3441            dialect=dialect,
3442            copy=copy,
3443            **opts,
3444        )
3445
3446    def set_(
3447        self,
3448        *expressions: ExpOrStr,
3449        append: bool = True,
3450        dialect: DialectType = None,
3451        copy: bool = True,
3452        **opts,
3453    ) -> Update:
3454        """
3455        Append to or set the SET expressions.
3456
3457        Example:
3458            >>> Update().table("my_table").set_("x = 1").sql()
3459            'UPDATE my_table SET x = 1'
3460
3461        Args:
3462            *expressions: the SQL code strings to parse.
3463                If `Expression` instance(s) are passed, they will be used as-is.
3464                Multiple expressions are combined with a comma.
3465            append: if `True`, add the new expressions to any existing SET expressions.
3466                Otherwise, this resets the expressions.
3467            dialect: the dialect used to parse the input expressions.
3468            copy: if `False`, modify this expression instance in-place.
3469            opts: other options to use to parse the input expressions.
3470        """
3471        return _apply_list_builder(
3472            *expressions,
3473            instance=self,
3474            arg="expressions",
3475            append=append,
3476            into=Expression,
3477            prefix=None,
3478            dialect=dialect,
3479            copy=copy,
3480            **opts,
3481        )
3482
3483    def where(
3484        self,
3485        *expressions: t.Optional[ExpOrStr],
3486        append: bool = True,
3487        dialect: DialectType = None,
3488        copy: bool = True,
3489        **opts,
3490    ) -> Select:
3491        """
3492        Append to or set the WHERE expressions.
3493
3494        Example:
3495            >>> Update().table("tbl").set_("x = 1").where("x = 'a' OR x < 'b'").sql()
3496            "UPDATE tbl SET x = 1 WHERE x = 'a' OR x < 'b'"
3497
3498        Args:
3499            *expressions: the SQL code strings to parse.
3500                If an `Expression` instance is passed, it will be used as-is.
3501                Multiple expressions are combined with an AND operator.
3502            append: if `True`, AND the new expressions to any existing expression.
3503                Otherwise, this resets the expression.
3504            dialect: the dialect used to parse the input expressions.
3505            copy: if `False`, modify this expression instance in-place.
3506            opts: other options to use to parse the input expressions.
3507
3508        Returns:
3509            Select: the modified expression.
3510        """
3511        return _apply_conjunction_builder(
3512            *expressions,
3513            instance=self,
3514            arg="where",
3515            append=append,
3516            into=Where,
3517            dialect=dialect,
3518            copy=copy,
3519            **opts,
3520        )
3521
3522    def from_(
3523        self,
3524        expression: t.Optional[ExpOrStr] = None,
3525        dialect: DialectType = None,
3526        copy: bool = True,
3527        **opts,
3528    ) -> Update:
3529        """
3530        Set the FROM expression.
3531
3532        Example:
3533            >>> Update().table("my_table").set_("x = 1").from_("baz").sql()
3534            'UPDATE my_table SET x = 1 FROM baz'
3535
3536        Args:
3537            expression : the SQL code strings to parse.
3538                If a `From` instance is passed, this is used as-is.
3539                If another `Expression` instance is passed, it will be wrapped in a `From`.
3540                If nothing is passed in then a from is not applied to the expression
3541            dialect: the dialect used to parse the input expression.
3542            copy: if `False`, modify this expression instance in-place.
3543            opts: other options to use to parse the input expressions.
3544
3545        Returns:
3546            The modified Update expression.
3547        """
3548        if not expression:
3549            return maybe_copy(self, copy)
3550
3551        return _apply_builder(
3552            expression=expression,
3553            instance=self,
3554            arg="from",
3555            into=From,
3556            prefix="FROM",
3557            dialect=dialect,
3558            copy=copy,
3559            **opts,
3560        )
3561
3562    def with_(
3563        self,
3564        alias: ExpOrStr,
3565        as_: ExpOrStr,
3566        recursive: t.Optional[bool] = None,
3567        materialized: t.Optional[bool] = None,
3568        append: bool = True,
3569        dialect: DialectType = None,
3570        copy: bool = True,
3571        **opts,
3572    ) -> Update:
3573        """
3574        Append to or set the common table expressions.
3575
3576        Example:
3577            >>> Update().table("my_table").set_("x = 1").from_("baz").with_("baz", "SELECT id FROM foo").sql()
3578            'WITH baz AS (SELECT id FROM foo) UPDATE my_table SET x = 1 FROM baz'
3579
3580        Args:
3581            alias: the SQL code string to parse as the table name.
3582                If an `Expression` instance is passed, this is used as-is.
3583            as_: the SQL code string to parse as the table expression.
3584                If an `Expression` instance is passed, it will be used as-is.
3585            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
3586            materialized: set the MATERIALIZED part of the expression.
3587            append: if `True`, add to any existing expressions.
3588                Otherwise, this resets the expressions.
3589            dialect: the dialect used to parse the input expression.
3590            copy: if `False`, modify this expression instance in-place.
3591            opts: other options to use to parse the input expressions.
3592
3593        Returns:
3594            The modified expression.
3595        """
3596        return _apply_cte_builder(
3597            self,
3598            alias,
3599            as_,
3600            recursive=recursive,
3601            materialized=materialized,
3602            append=append,
3603            dialect=dialect,
3604            copy=copy,
3605            **opts,
3606        )
3607
3608
3609class Values(UDTF):
3610    arg_types = {"expressions": True, "alias": False}
3611
3612
3613class Var(Expression):
3614    pass
3615
3616
3617class Version(Expression):
3618    """
3619    Time travel, iceberg, bigquery etc
3620    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
3621    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
3622    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
3623    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
3624    this is either TIMESTAMP or VERSION
3625    kind is ("AS OF", "BETWEEN")
3626    """
3627
3628    arg_types = {"this": True, "kind": True, "expression": False}
3629
3630
3631class Schema(Expression):
3632    arg_types = {"this": False, "expressions": False}
3633
3634
3635# https://dev.mysql.com/doc/refman/8.0/en/select.html
3636# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
3637class Lock(Expression):
3638    arg_types = {"update": True, "expressions": False, "wait": False}
3639
3640
3641class Select(Query):
3642    arg_types = {
3643        "with": False,
3644        "kind": False,
3645        "expressions": False,
3646        "hint": False,
3647        "distinct": False,
3648        "into": False,
3649        "from": False,
3650        "operation_modifiers": False,
3651        **QUERY_MODIFIERS,
3652    }
3653
3654    def from_(
3655        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
3656    ) -> Select:
3657        """
3658        Set the FROM expression.
3659
3660        Example:
3661            >>> Select().from_("tbl").select("x").sql()
3662            'SELECT x FROM tbl'
3663
3664        Args:
3665            expression : the SQL code strings to parse.
3666                If a `From` instance is passed, this is used as-is.
3667                If another `Expression` instance is passed, it will be wrapped in a `From`.
3668            dialect: the dialect used to parse the input expression.
3669            copy: if `False`, modify this expression instance in-place.
3670            opts: other options to use to parse the input expressions.
3671
3672        Returns:
3673            The modified Select expression.
3674        """
3675        return _apply_builder(
3676            expression=expression,
3677            instance=self,
3678            arg="from",
3679            into=From,
3680            prefix="FROM",
3681            dialect=dialect,
3682            copy=copy,
3683            **opts,
3684        )
3685
3686    def group_by(
3687        self,
3688        *expressions: t.Optional[ExpOrStr],
3689        append: bool = True,
3690        dialect: DialectType = None,
3691        copy: bool = True,
3692        **opts,
3693    ) -> Select:
3694        """
3695        Set the GROUP BY expression.
3696
3697        Example:
3698            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
3699            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
3700
3701        Args:
3702            *expressions: the SQL code strings to parse.
3703                If a `Group` instance is passed, this is used as-is.
3704                If another `Expression` instance is passed, it will be wrapped in a `Group`.
3705                If nothing is passed in then a group by is not applied to the expression
3706            append: if `True`, add to any existing expressions.
3707                Otherwise, this flattens all the `Group` expression into a single expression.
3708            dialect: the dialect used to parse the input expression.
3709            copy: if `False`, modify this expression instance in-place.
3710            opts: other options to use to parse the input expressions.
3711
3712        Returns:
3713            The modified Select expression.
3714        """
3715        if not expressions:
3716            return self if not copy else self.copy()
3717
3718        return _apply_child_list_builder(
3719            *expressions,
3720            instance=self,
3721            arg="group",
3722            append=append,
3723            copy=copy,
3724            prefix="GROUP BY",
3725            into=Group,
3726            dialect=dialect,
3727            **opts,
3728        )
3729
3730    def sort_by(
3731        self,
3732        *expressions: t.Optional[ExpOrStr],
3733        append: bool = True,
3734        dialect: DialectType = None,
3735        copy: bool = True,
3736        **opts,
3737    ) -> Select:
3738        """
3739        Set the SORT BY expression.
3740
3741        Example:
3742            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
3743            'SELECT x FROM tbl SORT BY x DESC'
3744
3745        Args:
3746            *expressions: the SQL code strings to parse.
3747                If a `Group` instance is passed, this is used as-is.
3748                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
3749            append: if `True`, add to any existing expressions.
3750                Otherwise, this flattens all the `Order` expression into a single expression.
3751            dialect: the dialect used to parse the input expression.
3752            copy: if `False`, modify this expression instance in-place.
3753            opts: other options to use to parse the input expressions.
3754
3755        Returns:
3756            The modified Select expression.
3757        """
3758        return _apply_child_list_builder(
3759            *expressions,
3760            instance=self,
3761            arg="sort",
3762            append=append,
3763            copy=copy,
3764            prefix="SORT BY",
3765            into=Sort,
3766            dialect=dialect,
3767            **opts,
3768        )
3769
3770    def cluster_by(
3771        self,
3772        *expressions: t.Optional[ExpOrStr],
3773        append: bool = True,
3774        dialect: DialectType = None,
3775        copy: bool = True,
3776        **opts,
3777    ) -> Select:
3778        """
3779        Set the CLUSTER BY expression.
3780
3781        Example:
3782            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
3783            'SELECT x FROM tbl CLUSTER BY x DESC'
3784
3785        Args:
3786            *expressions: the SQL code strings to parse.
3787                If a `Group` instance is passed, this is used as-is.
3788                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
3789            append: if `True`, add to any existing expressions.
3790                Otherwise, this flattens all the `Order` expression into a single expression.
3791            dialect: the dialect used to parse the input expression.
3792            copy: if `False`, modify this expression instance in-place.
3793            opts: other options to use to parse the input expressions.
3794
3795        Returns:
3796            The modified Select expression.
3797        """
3798        return _apply_child_list_builder(
3799            *expressions,
3800            instance=self,
3801            arg="cluster",
3802            append=append,
3803            copy=copy,
3804            prefix="CLUSTER BY",
3805            into=Cluster,
3806            dialect=dialect,
3807            **opts,
3808        )
3809
3810    def select(
3811        self,
3812        *expressions: t.Optional[ExpOrStr],
3813        append: bool = True,
3814        dialect: DialectType = None,
3815        copy: bool = True,
3816        **opts,
3817    ) -> Select:
3818        return _apply_list_builder(
3819            *expressions,
3820            instance=self,
3821            arg="expressions",
3822            append=append,
3823            dialect=dialect,
3824            into=Expression,
3825            copy=copy,
3826            **opts,
3827        )
3828
3829    def lateral(
3830        self,
3831        *expressions: t.Optional[ExpOrStr],
3832        append: bool = True,
3833        dialect: DialectType = None,
3834        copy: bool = True,
3835        **opts,
3836    ) -> Select:
3837        """
3838        Append to or set the LATERAL expressions.
3839
3840        Example:
3841            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3842            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3843
3844        Args:
3845            *expressions: the SQL code strings to parse.
3846                If an `Expression` instance is passed, it will be used as-is.
3847            append: if `True`, add to any existing expressions.
3848                Otherwise, this resets the expressions.
3849            dialect: the dialect used to parse the input expressions.
3850            copy: if `False`, modify this expression instance in-place.
3851            opts: other options to use to parse the input expressions.
3852
3853        Returns:
3854            The modified Select expression.
3855        """
3856        return _apply_list_builder(
3857            *expressions,
3858            instance=self,
3859            arg="laterals",
3860            append=append,
3861            into=Lateral,
3862            prefix="LATERAL VIEW",
3863            dialect=dialect,
3864            copy=copy,
3865            **opts,
3866        )
3867
3868    def join(
3869        self,
3870        expression: ExpOrStr,
3871        on: t.Optional[ExpOrStr] = None,
3872        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3873        append: bool = True,
3874        join_type: t.Optional[str] = None,
3875        join_alias: t.Optional[Identifier | str] = None,
3876        dialect: DialectType = None,
3877        copy: bool = True,
3878        **opts,
3879    ) -> Select:
3880        """
3881        Append to or set the JOIN expressions.
3882
3883        Example:
3884            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3885            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3886
3887            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3888            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3889
3890            Use `join_type` to change the type of join:
3891
3892            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3893            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3894
3895        Args:
3896            expression: the SQL code string to parse.
3897                If an `Expression` instance is passed, it will be used as-is.
3898            on: optionally specify the join "on" criteria as a SQL string.
3899                If an `Expression` instance is passed, it will be used as-is.
3900            using: optionally specify the join "using" criteria as a SQL string.
3901                If an `Expression` instance is passed, it will be used as-is.
3902            append: if `True`, add to any existing expressions.
3903                Otherwise, this resets the expressions.
3904            join_type: if set, alter the parsed join type.
3905            join_alias: an optional alias for the joined source.
3906            dialect: the dialect used to parse the input expressions.
3907            copy: if `False`, modify this expression instance in-place.
3908            opts: other options to use to parse the input expressions.
3909
3910        Returns:
3911            Select: the modified expression.
3912        """
3913        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3914
3915        try:
3916            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3917        except ParseError:
3918            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3919
3920        join = expression if isinstance(expression, Join) else Join(this=expression)
3921
3922        if isinstance(join.this, Select):
3923            join.this.replace(join.this.subquery())
3924
3925        if join_type:
3926            method: t.Optional[Token]
3927            side: t.Optional[Token]
3928            kind: t.Optional[Token]
3929
3930            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3931
3932            if method:
3933                join.set("method", method.text)
3934            if side:
3935                join.set("side", side.text)
3936            if kind:
3937                join.set("kind", kind.text)
3938
3939        if on:
3940            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3941            join.set("on", on)
3942
3943        if using:
3944            join = _apply_list_builder(
3945                *ensure_list(using),
3946                instance=join,
3947                arg="using",
3948                append=append,
3949                copy=copy,
3950                into=Identifier,
3951                **opts,
3952            )
3953
3954        if join_alias:
3955            join.set("this", alias_(join.this, join_alias, table=True))
3956
3957        return _apply_list_builder(
3958            join,
3959            instance=self,
3960            arg="joins",
3961            append=append,
3962            copy=copy,
3963            **opts,
3964        )
3965
3966    def where(
3967        self,
3968        *expressions: t.Optional[ExpOrStr],
3969        append: bool = True,
3970        dialect: DialectType = None,
3971        copy: bool = True,
3972        **opts,
3973    ) -> Select:
3974        """
3975        Append to or set the WHERE expressions.
3976
3977        Example:
3978            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3979            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3980
3981        Args:
3982            *expressions: the SQL code strings to parse.
3983                If an `Expression` instance is passed, it will be used as-is.
3984                Multiple expressions are combined with an AND operator.
3985            append: if `True`, AND the new expressions to any existing expression.
3986                Otherwise, this resets the expression.
3987            dialect: the dialect used to parse the input expressions.
3988            copy: if `False`, modify this expression instance in-place.
3989            opts: other options to use to parse the input expressions.
3990
3991        Returns:
3992            Select: the modified expression.
3993        """
3994        return _apply_conjunction_builder(
3995            *expressions,
3996            instance=self,
3997            arg="where",
3998            append=append,
3999            into=Where,
4000            dialect=dialect,
4001            copy=copy,
4002            **opts,
4003        )
4004
4005    def having(
4006        self,
4007        *expressions: t.Optional[ExpOrStr],
4008        append: bool = True,
4009        dialect: DialectType = None,
4010        copy: bool = True,
4011        **opts,
4012    ) -> Select:
4013        """
4014        Append to or set the HAVING expressions.
4015
4016        Example:
4017            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
4018            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
4019
4020        Args:
4021            *expressions: the SQL code strings to parse.
4022                If an `Expression` instance is passed, it will be used as-is.
4023                Multiple expressions are combined with an AND operator.
4024            append: if `True`, AND the new expressions to any existing expression.
4025                Otherwise, this resets the expression.
4026            dialect: the dialect used to parse the input expressions.
4027            copy: if `False`, modify this expression instance in-place.
4028            opts: other options to use to parse the input expressions.
4029
4030        Returns:
4031            The modified Select expression.
4032        """
4033        return _apply_conjunction_builder(
4034            *expressions,
4035            instance=self,
4036            arg="having",
4037            append=append,
4038            into=Having,
4039            dialect=dialect,
4040            copy=copy,
4041            **opts,
4042        )
4043
4044    def window(
4045        self,
4046        *expressions: t.Optional[ExpOrStr],
4047        append: bool = True,
4048        dialect: DialectType = None,
4049        copy: bool = True,
4050        **opts,
4051    ) -> Select:
4052        return _apply_list_builder(
4053            *expressions,
4054            instance=self,
4055            arg="windows",
4056            append=append,
4057            into=Window,
4058            dialect=dialect,
4059            copy=copy,
4060            **opts,
4061        )
4062
4063    def qualify(
4064        self,
4065        *expressions: t.Optional[ExpOrStr],
4066        append: bool = True,
4067        dialect: DialectType = None,
4068        copy: bool = True,
4069        **opts,
4070    ) -> Select:
4071        return _apply_conjunction_builder(
4072            *expressions,
4073            instance=self,
4074            arg="qualify",
4075            append=append,
4076            into=Qualify,
4077            dialect=dialect,
4078            copy=copy,
4079            **opts,
4080        )
4081
4082    def distinct(
4083        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
4084    ) -> Select:
4085        """
4086        Set the OFFSET expression.
4087
4088        Example:
4089            >>> Select().from_("tbl").select("x").distinct().sql()
4090            'SELECT DISTINCT x FROM tbl'
4091
4092        Args:
4093            ons: the expressions to distinct on
4094            distinct: whether the Select should be distinct
4095            copy: if `False`, modify this expression instance in-place.
4096
4097        Returns:
4098            Select: the modified expression.
4099        """
4100        instance = maybe_copy(self, copy)
4101        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
4102        instance.set("distinct", Distinct(on=on) if distinct else None)
4103        return instance
4104
4105    def ctas(
4106        self,
4107        table: ExpOrStr,
4108        properties: t.Optional[t.Dict] = None,
4109        dialect: DialectType = None,
4110        copy: bool = True,
4111        **opts,
4112    ) -> Create:
4113        """
4114        Convert this expression to a CREATE TABLE AS statement.
4115
4116        Example:
4117            >>> Select().select("*").from_("tbl").ctas("x").sql()
4118            'CREATE TABLE x AS SELECT * FROM tbl'
4119
4120        Args:
4121            table: the SQL code string to parse as the table name.
4122                If another `Expression` instance is passed, it will be used as-is.
4123            properties: an optional mapping of table properties
4124            dialect: the dialect used to parse the input table.
4125            copy: if `False`, modify this expression instance in-place.
4126            opts: other options to use to parse the input table.
4127
4128        Returns:
4129            The new Create expression.
4130        """
4131        instance = maybe_copy(self, copy)
4132        table_expression = maybe_parse(table, into=Table, dialect=dialect, **opts)
4133
4134        properties_expression = None
4135        if properties:
4136            properties_expression = Properties.from_dict(properties)
4137
4138        return Create(
4139            this=table_expression,
4140            kind="TABLE",
4141            expression=instance,
4142            properties=properties_expression,
4143        )
4144
4145    def lock(self, update: bool = True, copy: bool = True) -> Select:
4146        """
4147        Set the locking read mode for this expression.
4148
4149        Examples:
4150            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
4151            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
4152
4153            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
4154            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
4155
4156        Args:
4157            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
4158            copy: if `False`, modify this expression instance in-place.
4159
4160        Returns:
4161            The modified expression.
4162        """
4163        inst = maybe_copy(self, copy)
4164        inst.set("locks", [Lock(update=update)])
4165
4166        return inst
4167
4168    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
4169        """
4170        Set hints for this expression.
4171
4172        Examples:
4173            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
4174            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
4175
4176        Args:
4177            hints: The SQL code strings to parse as the hints.
4178                If an `Expression` instance is passed, it will be used as-is.
4179            dialect: The dialect used to parse the hints.
4180            copy: If `False`, modify this expression instance in-place.
4181
4182        Returns:
4183            The modified expression.
4184        """
4185        inst = maybe_copy(self, copy)
4186        inst.set(
4187            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
4188        )
4189
4190        return inst
4191
4192    @property
4193    def named_selects(self) -> t.List[str]:
4194        return [e.output_name for e in self.expressions if e.alias_or_name]
4195
4196    @property
4197    def is_star(self) -> bool:
4198        return any(expression.is_star for expression in self.expressions)
4199
4200    @property
4201    def selects(self) -> t.List[Expression]:
4202        return self.expressions
4203
4204
4205UNWRAPPED_QUERIES = (Select, SetOperation)
4206
4207
4208class Subquery(DerivedTable, Query):
4209    arg_types = {
4210        "this": True,
4211        "alias": False,
4212        "with": False,
4213        **QUERY_MODIFIERS,
4214    }
4215
4216    def unnest(self):
4217        """Returns the first non subquery."""
4218        expression = self
4219        while isinstance(expression, Subquery):
4220            expression = expression.this
4221        return expression
4222
4223    def unwrap(self) -> Subquery:
4224        expression = self
4225        while expression.same_parent and expression.is_wrapper:
4226            expression = t.cast(Subquery, expression.parent)
4227        return expression
4228
4229    def select(
4230        self,
4231        *expressions: t.Optional[ExpOrStr],
4232        append: bool = True,
4233        dialect: DialectType = None,
4234        copy: bool = True,
4235        **opts,
4236    ) -> Subquery:
4237        this = maybe_copy(self, copy)
4238        this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
4239        return this
4240
4241    @property
4242    def is_wrapper(self) -> bool:
4243        """
4244        Whether this Subquery acts as a simple wrapper around another expression.
4245
4246        SELECT * FROM (((SELECT * FROM t)))
4247                      ^
4248                      This corresponds to a "wrapper" Subquery node
4249        """
4250        return all(v is None for k, v in self.args.items() if k != "this")
4251
4252    @property
4253    def is_star(self) -> bool:
4254        return self.this.is_star
4255
4256    @property
4257    def output_name(self) -> str:
4258        return self.alias
4259
4260
4261class TableSample(Expression):
4262    arg_types = {
4263        "expressions": False,
4264        "method": False,
4265        "bucket_numerator": False,
4266        "bucket_denominator": False,
4267        "bucket_field": False,
4268        "percent": False,
4269        "rows": False,
4270        "size": False,
4271        "seed": False,
4272    }
4273
4274
4275class Tag(Expression):
4276    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
4277
4278    arg_types = {
4279        "this": False,
4280        "prefix": False,
4281        "postfix": False,
4282    }
4283
4284
4285# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
4286# https://duckdb.org/docs/sql/statements/pivot
4287class Pivot(Expression):
4288    arg_types = {
4289        "this": False,
4290        "alias": False,
4291        "expressions": False,
4292        "field": False,
4293        "unpivot": False,
4294        "using": False,
4295        "group": False,
4296        "columns": False,
4297        "include_nulls": False,
4298        "default_on_null": False,
4299        "into": False,
4300    }
4301
4302    @property
4303    def unpivot(self) -> bool:
4304        return bool(self.args.get("unpivot"))
4305
4306
4307# https://duckdb.org/docs/sql/statements/unpivot#simplified-unpivot-syntax
4308# UNPIVOT ... INTO [NAME <col_name> VALUE <col_value>][...,]
4309class UnpivotColumns(Expression):
4310    arg_types = {"this": True, "expressions": True}
4311
4312
4313class Window(Condition):
4314    arg_types = {
4315        "this": True,
4316        "partition_by": False,
4317        "order": False,
4318        "spec": False,
4319        "alias": False,
4320        "over": False,
4321        "first": False,
4322    }
4323
4324
4325class WindowSpec(Expression):
4326    arg_types = {
4327        "kind": False,
4328        "start": False,
4329        "start_side": False,
4330        "end": False,
4331        "end_side": False,
4332    }
4333
4334
4335class PreWhere(Expression):
4336    pass
4337
4338
4339class Where(Expression):
4340    pass
4341
4342
4343class Star(Expression):
4344    arg_types = {"except": False, "replace": False, "rename": False}
4345
4346    @property
4347    def name(self) -> str:
4348        return "*"
4349
4350    @property
4351    def output_name(self) -> str:
4352        return self.name
4353
4354
4355class Parameter(Condition):
4356    arg_types = {"this": True, "expression": False}
4357
4358
4359class SessionParameter(Condition):
4360    arg_types = {"this": True, "kind": False}
4361
4362
4363class Placeholder(Condition):
4364    arg_types = {"this": False, "kind": False}
4365
4366    @property
4367    def name(self) -> str:
4368        return self.this or "?"
4369
4370
4371class Null(Condition):
4372    arg_types: t.Dict[str, t.Any] = {}
4373
4374    @property
4375    def name(self) -> str:
4376        return "NULL"
4377
4378    def to_py(self) -> Lit[None]:
4379        return None
4380
4381
4382class Boolean(Condition):
4383    def to_py(self) -> bool:
4384        return self.this
4385
4386
4387class DataTypeParam(Expression):
4388    arg_types = {"this": True, "expression": False}
4389
4390    @property
4391    def name(self) -> str:
4392        return self.this.name
4393
4394
4395# The `nullable` arg is helpful when transpiling types from other dialects to ClickHouse, which
4396# assumes non-nullable types by default. Values `None` and `True` mean the type is nullable.
4397class DataType(Expression):
4398    arg_types = {
4399        "this": True,
4400        "expressions": False,
4401        "nested": False,
4402        "values": False,
4403        "prefix": False,
4404        "kind": False,
4405        "nullable": False,
4406    }
4407
4408    class Type(AutoName):
4409        ARRAY = auto()
4410        AGGREGATEFUNCTION = auto()
4411        SIMPLEAGGREGATEFUNCTION = auto()
4412        BIGDECIMAL = auto()
4413        BIGINT = auto()
4414        BIGSERIAL = auto()
4415        BINARY = auto()
4416        BIT = auto()
4417        BLOB = auto()
4418        BOOLEAN = auto()
4419        BPCHAR = auto()
4420        CHAR = auto()
4421        DATE = auto()
4422        DATE32 = auto()
4423        DATEMULTIRANGE = auto()
4424        DATERANGE = auto()
4425        DATETIME = auto()
4426        DATETIME2 = auto()
4427        DATETIME64 = auto()
4428        DECIMAL = auto()
4429        DECIMAL32 = auto()
4430        DECIMAL64 = auto()
4431        DECIMAL128 = auto()
4432        DECIMAL256 = auto()
4433        DOUBLE = auto()
4434        DYNAMIC = auto()
4435        ENUM = auto()
4436        ENUM8 = auto()
4437        ENUM16 = auto()
4438        FIXEDSTRING = auto()
4439        FLOAT = auto()
4440        GEOGRAPHY = auto()
4441        GEOMETRY = auto()
4442        POINT = auto()
4443        RING = auto()
4444        LINESTRING = auto()
4445        MULTILINESTRING = auto()
4446        POLYGON = auto()
4447        MULTIPOLYGON = auto()
4448        HLLSKETCH = auto()
4449        HSTORE = auto()
4450        IMAGE = auto()
4451        INET = auto()
4452        INT = auto()
4453        INT128 = auto()
4454        INT256 = auto()
4455        INT4MULTIRANGE = auto()
4456        INT4RANGE = auto()
4457        INT8MULTIRANGE = auto()
4458        INT8RANGE = auto()
4459        INTERVAL = auto()
4460        IPADDRESS = auto()
4461        IPPREFIX = auto()
4462        IPV4 = auto()
4463        IPV6 = auto()
4464        JSON = auto()
4465        JSONB = auto()
4466        LIST = auto()
4467        LONGBLOB = auto()
4468        LONGTEXT = auto()
4469        LOWCARDINALITY = auto()
4470        MAP = auto()
4471        MEDIUMBLOB = auto()
4472        MEDIUMINT = auto()
4473        MEDIUMTEXT = auto()
4474        MONEY = auto()
4475        NAME = auto()
4476        NCHAR = auto()
4477        NESTED = auto()
4478        NULL = auto()
4479        NUMMULTIRANGE = auto()
4480        NUMRANGE = auto()
4481        NVARCHAR = auto()
4482        OBJECT = auto()
4483        RANGE = auto()
4484        ROWVERSION = auto()
4485        SERIAL = auto()
4486        SET = auto()
4487        SMALLDATETIME = auto()
4488        SMALLINT = auto()
4489        SMALLMONEY = auto()
4490        SMALLSERIAL = auto()
4491        STRUCT = auto()
4492        SUPER = auto()
4493        TEXT = auto()
4494        TINYBLOB = auto()
4495        TINYTEXT = auto()
4496        TIME = auto()
4497        TIMETZ = auto()
4498        TIMESTAMP = auto()
4499        TIMESTAMPNTZ = auto()
4500        TIMESTAMPLTZ = auto()
4501        TIMESTAMPTZ = auto()
4502        TIMESTAMP_S = auto()
4503        TIMESTAMP_MS = auto()
4504        TIMESTAMP_NS = auto()
4505        TINYINT = auto()
4506        TSMULTIRANGE = auto()
4507        TSRANGE = auto()
4508        TSTZMULTIRANGE = auto()
4509        TSTZRANGE = auto()
4510        UBIGINT = auto()
4511        UINT = auto()
4512        UINT128 = auto()
4513        UINT256 = auto()
4514        UMEDIUMINT = auto()
4515        UDECIMAL = auto()
4516        UDOUBLE = auto()
4517        UNION = auto()
4518        UNKNOWN = auto()  # Sentinel value, useful for type annotation
4519        USERDEFINED = "USER-DEFINED"
4520        USMALLINT = auto()
4521        UTINYINT = auto()
4522        UUID = auto()
4523        VARBINARY = auto()
4524        VARCHAR = auto()
4525        VARIANT = auto()
4526        VECTOR = auto()
4527        XML = auto()
4528        YEAR = auto()
4529        TDIGEST = auto()
4530
4531    STRUCT_TYPES = {
4532        Type.NESTED,
4533        Type.OBJECT,
4534        Type.STRUCT,
4535        Type.UNION,
4536    }
4537
4538    ARRAY_TYPES = {
4539        Type.ARRAY,
4540        Type.LIST,
4541    }
4542
4543    NESTED_TYPES = {
4544        *STRUCT_TYPES,
4545        *ARRAY_TYPES,
4546        Type.MAP,
4547    }
4548
4549    TEXT_TYPES = {
4550        Type.CHAR,
4551        Type.NCHAR,
4552        Type.NVARCHAR,
4553        Type.TEXT,
4554        Type.VARCHAR,
4555        Type.NAME,
4556    }
4557
4558    SIGNED_INTEGER_TYPES = {
4559        Type.BIGINT,
4560        Type.INT,
4561        Type.INT128,
4562        Type.INT256,
4563        Type.MEDIUMINT,
4564        Type.SMALLINT,
4565        Type.TINYINT,
4566    }
4567
4568    UNSIGNED_INTEGER_TYPES = {
4569        Type.UBIGINT,
4570        Type.UINT,
4571        Type.UINT128,
4572        Type.UINT256,
4573        Type.UMEDIUMINT,
4574        Type.USMALLINT,
4575        Type.UTINYINT,
4576    }
4577
4578    INTEGER_TYPES = {
4579        *SIGNED_INTEGER_TYPES,
4580        *UNSIGNED_INTEGER_TYPES,
4581        Type.BIT,
4582    }
4583
4584    FLOAT_TYPES = {
4585        Type.DOUBLE,
4586        Type.FLOAT,
4587    }
4588
4589    REAL_TYPES = {
4590        *FLOAT_TYPES,
4591        Type.BIGDECIMAL,
4592        Type.DECIMAL,
4593        Type.DECIMAL32,
4594        Type.DECIMAL64,
4595        Type.DECIMAL128,
4596        Type.DECIMAL256,
4597        Type.MONEY,
4598        Type.SMALLMONEY,
4599        Type.UDECIMAL,
4600        Type.UDOUBLE,
4601    }
4602
4603    NUMERIC_TYPES = {
4604        *INTEGER_TYPES,
4605        *REAL_TYPES,
4606    }
4607
4608    TEMPORAL_TYPES = {
4609        Type.DATE,
4610        Type.DATE32,
4611        Type.DATETIME,
4612        Type.DATETIME2,
4613        Type.DATETIME64,
4614        Type.SMALLDATETIME,
4615        Type.TIME,
4616        Type.TIMESTAMP,
4617        Type.TIMESTAMPNTZ,
4618        Type.TIMESTAMPLTZ,
4619        Type.TIMESTAMPTZ,
4620        Type.TIMESTAMP_MS,
4621        Type.TIMESTAMP_NS,
4622        Type.TIMESTAMP_S,
4623        Type.TIMETZ,
4624    }
4625
4626    @classmethod
4627    def build(
4628        cls,
4629        dtype: DATA_TYPE,
4630        dialect: DialectType = None,
4631        udt: bool = False,
4632        copy: bool = True,
4633        **kwargs,
4634    ) -> DataType:
4635        """
4636        Constructs a DataType object.
4637
4638        Args:
4639            dtype: the data type of interest.
4640            dialect: the dialect to use for parsing `dtype`, in case it's a string.
4641            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
4642                DataType, thus creating a user-defined type.
4643            copy: whether to copy the data type.
4644            kwargs: additional arguments to pass in the constructor of DataType.
4645
4646        Returns:
4647            The constructed DataType object.
4648        """
4649        from sqlglot import parse_one
4650
4651        if isinstance(dtype, str):
4652            if dtype.upper() == "UNKNOWN":
4653                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
4654
4655            try:
4656                data_type_exp = parse_one(
4657                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
4658                )
4659            except ParseError:
4660                if udt:
4661                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
4662                raise
4663        elif isinstance(dtype, DataType.Type):
4664            data_type_exp = DataType(this=dtype)
4665        elif isinstance(dtype, DataType):
4666            return maybe_copy(dtype, copy)
4667        else:
4668            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
4669
4670        return DataType(**{**data_type_exp.args, **kwargs})
4671
4672    def is_type(self, *dtypes: DATA_TYPE, check_nullable: bool = False) -> bool:
4673        """
4674        Checks whether this DataType matches one of the provided data types. Nested types or precision
4675        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
4676
4677        Args:
4678            dtypes: the data types to compare this DataType to.
4679            check_nullable: whether to take the NULLABLE type constructor into account for the comparison.
4680                If false, it means that NULLABLE<INT> is equivalent to INT.
4681
4682        Returns:
4683            True, if and only if there is a type in `dtypes` which is equal to this DataType.
4684        """
4685        self_is_nullable = self.args.get("nullable")
4686        for dtype in dtypes:
4687            other_type = DataType.build(dtype, copy=False, udt=True)
4688            other_is_nullable = other_type.args.get("nullable")
4689            if (
4690                other_type.expressions
4691                or (check_nullable and (self_is_nullable or other_is_nullable))
4692                or self.this == DataType.Type.USERDEFINED
4693                or other_type.this == DataType.Type.USERDEFINED
4694            ):
4695                matches = self == other_type
4696            else:
4697                matches = self.this == other_type.this
4698
4699            if matches:
4700                return True
4701        return False
4702
4703
4704DATA_TYPE = t.Union[str, DataType, DataType.Type]
4705
4706
4707# https://www.postgresql.org/docs/15/datatype-pseudo.html
4708class PseudoType(DataType):
4709    arg_types = {"this": True}
4710
4711
4712# https://www.postgresql.org/docs/15/datatype-oid.html
4713class ObjectIdentifier(DataType):
4714    arg_types = {"this": True}
4715
4716
4717# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
4718class SubqueryPredicate(Predicate):
4719    pass
4720
4721
4722class All(SubqueryPredicate):
4723    pass
4724
4725
4726class Any(SubqueryPredicate):
4727    pass
4728
4729
4730# Commands to interact with the databases or engines. For most of the command
4731# expressions we parse whatever comes after the command's name as a string.
4732class Command(Expression):
4733    arg_types = {"this": True, "expression": False}
4734
4735
4736class Transaction(Expression):
4737    arg_types = {"this": False, "modes": False, "mark": False}
4738
4739
4740class Commit(Expression):
4741    arg_types = {"chain": False, "this": False, "durability": False}
4742
4743
4744class Rollback(Expression):
4745    arg_types = {"savepoint": False, "this": False}
4746
4747
4748class Alter(Expression):
4749    arg_types = {
4750        "this": True,
4751        "kind": True,
4752        "actions": True,
4753        "exists": False,
4754        "only": False,
4755        "options": False,
4756        "cluster": False,
4757        "not_valid": False,
4758    }
4759
4760    @property
4761    def kind(self) -> t.Optional[str]:
4762        kind = self.args.get("kind")
4763        return kind and kind.upper()
4764
4765    @property
4766    def actions(self) -> t.List[Expression]:
4767        return self.args.get("actions") or []
4768
4769
4770class Analyze(Expression):
4771    arg_types = {
4772        "kind": False,
4773        "this": False,
4774        "options": False,
4775        "mode": False,
4776        "partition": False,
4777        "expression": False,
4778        "properties": False,
4779    }
4780
4781
4782class AnalyzeStatistics(Expression):
4783    arg_types = {
4784        "kind": True,
4785        "option": False,
4786        "this": False,
4787        "expressions": False,
4788    }
4789
4790
4791class AnalyzeHistogram(Expression):
4792    arg_types = {
4793        "this": True,
4794        "expressions": True,
4795        "expression": False,
4796        "update_options": False,
4797    }
4798
4799
4800class AnalyzeSample(Expression):
4801    arg_types = {"kind": True, "sample": True}
4802
4803
4804class AnalyzeListChainedRows(Expression):
4805    arg_types = {"expression": False}
4806
4807
4808class AnalyzeDelete(Expression):
4809    arg_types = {"kind": False}
4810
4811
4812class AnalyzeWith(Expression):
4813    arg_types = {"expressions": True}
4814
4815
4816class AnalyzeValidate(Expression):
4817    arg_types = {
4818        "kind": True,
4819        "this": False,
4820        "expression": False,
4821    }
4822
4823
4824class AnalyzeColumns(Expression):
4825    pass
4826
4827
4828class UsingData(Expression):
4829    pass
4830
4831
4832class AddConstraint(Expression):
4833    arg_types = {"expressions": True}
4834
4835
4836class AttachOption(Expression):
4837    arg_types = {"this": True, "expression": False}
4838
4839
4840class DropPartition(Expression):
4841    arg_types = {"expressions": True, "exists": False}
4842
4843
4844# https://clickhouse.com/docs/en/sql-reference/statements/alter/partition#replace-partition
4845class ReplacePartition(Expression):
4846    arg_types = {"expression": True, "source": True}
4847
4848
4849# Binary expressions like (ADD a b)
4850class Binary(Condition):
4851    arg_types = {"this": True, "expression": True}
4852
4853    @property
4854    def left(self) -> Expression:
4855        return self.this
4856
4857    @property
4858    def right(self) -> Expression:
4859        return self.expression
4860
4861
4862class Add(Binary):
4863    pass
4864
4865
4866class Connector(Binary):
4867    pass
4868
4869
4870class BitwiseAnd(Binary):
4871    pass
4872
4873
4874class BitwiseLeftShift(Binary):
4875    pass
4876
4877
4878class BitwiseOr(Binary):
4879    pass
4880
4881
4882class BitwiseRightShift(Binary):
4883    pass
4884
4885
4886class BitwiseXor(Binary):
4887    pass
4888
4889
4890class Div(Binary):
4891    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
4892
4893
4894class Overlaps(Binary):
4895    pass
4896
4897
4898class Dot(Binary):
4899    @property
4900    def is_star(self) -> bool:
4901        return self.expression.is_star
4902
4903    @property
4904    def name(self) -> str:
4905        return self.expression.name
4906
4907    @property
4908    def output_name(self) -> str:
4909        return self.name
4910
4911    @classmethod
4912    def build(self, expressions: t.Sequence[Expression]) -> Dot:
4913        """Build a Dot object with a sequence of expressions."""
4914        if len(expressions) < 2:
4915            raise ValueError("Dot requires >= 2 expressions.")
4916
4917        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
4918
4919    @property
4920    def parts(self) -> t.List[Expression]:
4921        """Return the parts of a table / column in order catalog, db, table."""
4922        this, *parts = self.flatten()
4923
4924        parts.reverse()
4925
4926        for arg in COLUMN_PARTS:
4927            part = this.args.get(arg)
4928
4929            if isinstance(part, Expression):
4930                parts.append(part)
4931
4932        parts.reverse()
4933        return parts
4934
4935
4936class DPipe(Binary):
4937    arg_types = {"this": True, "expression": True, "safe": False}
4938
4939
4940class EQ(Binary, Predicate):
4941    pass
4942
4943
4944class NullSafeEQ(Binary, Predicate):
4945    pass
4946
4947
4948class NullSafeNEQ(Binary, Predicate):
4949    pass
4950
4951
4952# Represents e.g. := in DuckDB which is mostly used for setting parameters
4953class PropertyEQ(Binary):
4954    pass
4955
4956
4957class Distance(Binary):
4958    pass
4959
4960
4961class Escape(Binary):
4962    pass
4963
4964
4965class Glob(Binary, Predicate):
4966    pass
4967
4968
4969class GT(Binary, Predicate):
4970    pass
4971
4972
4973class GTE(Binary, Predicate):
4974    pass
4975
4976
4977class ILike(Binary, Predicate):
4978    pass
4979
4980
4981class ILikeAny(Binary, Predicate):
4982    pass
4983
4984
4985class IntDiv(Binary):
4986    pass
4987
4988
4989class Is(Binary, Predicate):
4990    pass
4991
4992
4993class Kwarg(Binary):
4994    """Kwarg in special functions like func(kwarg => y)."""
4995
4996
4997class Like(Binary, Predicate):
4998    pass
4999
5000
5001class LikeAny(Binary, Predicate):
5002    pass
5003
5004
5005class LT(Binary, Predicate):
5006    pass
5007
5008
5009class LTE(Binary, Predicate):
5010    pass
5011
5012
5013class Mod(Binary):
5014    pass
5015
5016
5017class Mul(Binary):
5018    pass
5019
5020
5021class NEQ(Binary, Predicate):
5022    pass
5023
5024
5025# https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH
5026class Operator(Binary):
5027    arg_types = {"this": True, "operator": True, "expression": True}
5028
5029
5030class SimilarTo(Binary, Predicate):
5031    pass
5032
5033
5034class Slice(Binary):
5035    arg_types = {"this": False, "expression": False}
5036
5037
5038class Sub(Binary):
5039    pass
5040
5041
5042# Unary Expressions
5043# (NOT a)
5044class Unary(Condition):
5045    pass
5046
5047
5048class BitwiseNot(Unary):
5049    pass
5050
5051
5052class Not(Unary):
5053    pass
5054
5055
5056class Paren(Unary):
5057    @property
5058    def output_name(self) -> str:
5059        return self.this.name
5060
5061
5062class Neg(Unary):
5063    def to_py(self) -> int | Decimal:
5064        if self.is_number:
5065            return self.this.to_py() * -1
5066        return super().to_py()
5067
5068
5069class Alias(Expression):
5070    arg_types = {"this": True, "alias": False}
5071
5072    @property
5073    def output_name(self) -> str:
5074        return self.alias
5075
5076
5077# BigQuery requires the UNPIVOT column list aliases to be either strings or ints, but
5078# other dialects require identifiers. This enables us to transpile between them easily.
5079class PivotAlias(Alias):
5080    pass
5081
5082
5083# Represents Snowflake's ANY [ ORDER BY ... ] syntax
5084# https://docs.snowflake.com/en/sql-reference/constructs/pivot
5085class PivotAny(Expression):
5086    arg_types = {"this": False}
5087
5088
5089class Aliases(Expression):
5090    arg_types = {"this": True, "expressions": True}
5091
5092    @property
5093    def aliases(self):
5094        return self.expressions
5095
5096
5097# https://docs.aws.amazon.com/redshift/latest/dg/query-super.html
5098class AtIndex(Expression):
5099    arg_types = {"this": True, "expression": True}
5100
5101
5102class AtTimeZone(Expression):
5103    arg_types = {"this": True, "zone": True}
5104
5105
5106class FromTimeZone(Expression):
5107    arg_types = {"this": True, "zone": True}
5108
5109
5110class Between(Predicate):
5111    arg_types = {"this": True, "low": True, "high": True}
5112
5113
5114class Bracket(Condition):
5115    # https://cloud.google.com/bigquery/docs/reference/standard-sql/operators#array_subscript_operator
5116    arg_types = {
5117        "this": True,
5118        "expressions": True,
5119        "offset": False,
5120        "safe": False,
5121        "returns_list_for_maps": False,
5122    }
5123
5124    @property
5125    def output_name(self) -> str:
5126        if len(self.expressions) == 1:
5127            return self.expressions[0].output_name
5128
5129        return super().output_name
5130
5131
5132class Distinct(Expression):
5133    arg_types = {"expressions": False, "on": False}
5134
5135
5136class In(Predicate):
5137    arg_types = {
5138        "this": True,
5139        "expressions": False,
5140        "query": False,
5141        "unnest": False,
5142        "field": False,
5143        "is_global": False,
5144    }
5145
5146
5147# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
5148class ForIn(Expression):
5149    arg_types = {"this": True, "expression": True}
5150
5151
5152class TimeUnit(Expression):
5153    """Automatically converts unit arg into a var."""
5154
5155    arg_types = {"unit": False}
5156
5157    UNABBREVIATED_UNIT_NAME = {
5158        "D": "DAY",
5159        "H": "HOUR",
5160        "M": "MINUTE",
5161        "MS": "MILLISECOND",
5162        "NS": "NANOSECOND",
5163        "Q": "QUARTER",
5164        "S": "SECOND",
5165        "US": "MICROSECOND",
5166        "W": "WEEK",
5167        "Y": "YEAR",
5168    }
5169
5170    VAR_LIKE = (Column, Literal, Var)
5171
5172    def __init__(self, **args):
5173        unit = args.get("unit")
5174        if isinstance(unit, self.VAR_LIKE):
5175            args["unit"] = Var(
5176                this=(self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name).upper()
5177            )
5178        elif isinstance(unit, Week):
5179            unit.set("this", Var(this=unit.this.name.upper()))
5180
5181        super().__init__(**args)
5182
5183    @property
5184    def unit(self) -> t.Optional[Var | IntervalSpan]:
5185        return self.args.get("unit")
5186
5187
5188class IntervalOp(TimeUnit):
5189    arg_types = {"unit": False, "expression": True}
5190
5191    def interval(self):
5192        return Interval(
5193            this=self.expression.copy(),
5194            unit=self.unit.copy() if self.unit else None,
5195        )
5196
5197
5198# https://www.oracletutorial.com/oracle-basics/oracle-interval/
5199# https://trino.io/docs/current/language/types.html#interval-day-to-second
5200# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
5201class IntervalSpan(DataType):
5202    arg_types = {"this": True, "expression": True}
5203
5204
5205class Interval(TimeUnit):
5206    arg_types = {"this": False, "unit": False}
5207
5208
5209class IgnoreNulls(Expression):
5210    pass
5211
5212
5213class RespectNulls(Expression):
5214    pass
5215
5216
5217# https://cloud.google.com/bigquery/docs/reference/standard-sql/aggregate-function-calls#max_min_clause
5218class HavingMax(Expression):
5219    arg_types = {"this": True, "expression": True, "max": True}
5220
5221
5222# Functions
5223class Func(Condition):
5224    """
5225    The base class for all function expressions.
5226
5227    Attributes:
5228        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
5229            treated as a variable length argument and the argument's value will be stored as a list.
5230        _sql_names (list): the SQL name (1st item in the list) and aliases (subsequent items) for this
5231            function expression. These values are used to map this node to a name during parsing as
5232            well as to provide the function's name during SQL string generation. By default the SQL
5233            name is set to the expression's class name transformed to snake case.
5234    """
5235
5236    is_var_len_args = False
5237
5238    @classmethod
5239    def from_arg_list(cls, args):
5240        if cls.is_var_len_args:
5241            all_arg_keys = list(cls.arg_types)
5242            # If this function supports variable length argument treat the last argument as such.
5243            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
5244            num_non_var = len(non_var_len_arg_keys)
5245
5246            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
5247            args_dict[all_arg_keys[-1]] = args[num_non_var:]
5248        else:
5249            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
5250
5251        return cls(**args_dict)
5252
5253    @classmethod
5254    def sql_names(cls):
5255        if cls is Func:
5256            raise NotImplementedError(
5257                "SQL name is only supported by concrete function implementations"
5258            )
5259        if "_sql_names" not in cls.__dict__:
5260            cls._sql_names = [camel_to_snake_case(cls.__name__)]
5261        return cls._sql_names
5262
5263    @classmethod
5264    def sql_name(cls):
5265        return cls.sql_names()[0]
5266
5267    @classmethod
5268    def default_parser_mappings(cls):
5269        return {name: cls.from_arg_list for name in cls.sql_names()}
5270
5271
5272class AggFunc(Func):
5273    pass
5274
5275
5276class ParameterizedAgg(AggFunc):
5277    arg_types = {"this": True, "expressions": True, "params": True}
5278
5279
5280class Abs(Func):
5281    pass
5282
5283
5284class ArgMax(AggFunc):
5285    arg_types = {"this": True, "expression": True, "count": False}
5286    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
5287
5288
5289class ArgMin(AggFunc):
5290    arg_types = {"this": True, "expression": True, "count": False}
5291    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
5292
5293
5294class ApproxTopK(AggFunc):
5295    arg_types = {"this": True, "expression": False, "counters": False}
5296
5297
5298class Flatten(Func):
5299    pass
5300
5301
5302# https://spark.apache.org/docs/latest/api/sql/index.html#transform
5303class Transform(Func):
5304    arg_types = {"this": True, "expression": True}
5305
5306
5307class Anonymous(Func):
5308    arg_types = {"this": True, "expressions": False}
5309    is_var_len_args = True
5310
5311    @property
5312    def name(self) -> str:
5313        return self.this if isinstance(self.this, str) else self.this.name
5314
5315
5316class AnonymousAggFunc(AggFunc):
5317    arg_types = {"this": True, "expressions": False}
5318    is_var_len_args = True
5319
5320
5321# https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators
5322class CombinedAggFunc(AnonymousAggFunc):
5323    arg_types = {"this": True, "expressions": False}
5324
5325
5326class CombinedParameterizedAgg(ParameterizedAgg):
5327    arg_types = {"this": True, "expressions": True, "params": True}
5328
5329
5330# https://docs.snowflake.com/en/sql-reference/functions/hll
5331# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
5332class Hll(AggFunc):
5333    arg_types = {"this": True, "expressions": False}
5334    is_var_len_args = True
5335
5336
5337class ApproxDistinct(AggFunc):
5338    arg_types = {"this": True, "accuracy": False}
5339    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
5340
5341
5342class Apply(Func):
5343    arg_types = {"this": True, "expression": True}
5344
5345
5346class Array(Func):
5347    arg_types = {"expressions": False, "bracket_notation": False}
5348    is_var_len_args = True
5349
5350
5351# https://docs.snowflake.com/en/sql-reference/functions/to_array
5352class ToArray(Func):
5353    pass
5354
5355
5356# https://materialize.com/docs/sql/types/list/
5357class List(Func):
5358    arg_types = {"expressions": False}
5359    is_var_len_args = True
5360
5361
5362# String pad, kind True -> LPAD, False -> RPAD
5363class Pad(Func):
5364    arg_types = {"this": True, "expression": True, "fill_pattern": False, "is_left": True}
5365
5366
5367# https://docs.snowflake.com/en/sql-reference/functions/to_char
5368# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_CHAR-number.html
5369class ToChar(Func):
5370    arg_types = {"this": True, "format": False, "nlsparam": False}
5371
5372
5373# https://docs.snowflake.com/en/sql-reference/functions/to_decimal
5374# https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/TO_NUMBER.html
5375class ToNumber(Func):
5376    arg_types = {
5377        "this": True,
5378        "format": False,
5379        "nlsparam": False,
5380        "precision": False,
5381        "scale": False,
5382    }
5383
5384
5385# https://docs.snowflake.com/en/sql-reference/functions/to_double
5386class ToDouble(Func):
5387    arg_types = {
5388        "this": True,
5389        "format": False,
5390    }
5391
5392
5393class Columns(Func):
5394    arg_types = {"this": True, "unpack": False}
5395
5396
5397# https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16#syntax
5398class Convert(Func):
5399    arg_types = {"this": True, "expression": True, "style": False}
5400
5401
5402class ConvertTimezone(Func):
5403    arg_types = {"source_tz": False, "target_tz": True, "timestamp": True}
5404
5405
5406class GenerateSeries(Func):
5407    arg_types = {"start": True, "end": True, "step": False, "is_end_exclusive": False}
5408
5409
5410# Postgres' GENERATE_SERIES function returns a row set, i.e. it implicitly explodes when it's
5411# used in a projection, so this expression is a helper that facilitates transpilation to other
5412# dialects. For example, we'd generate UNNEST(GENERATE_SERIES(...)) in DuckDB
5413class ExplodingGenerateSeries(GenerateSeries):
5414    pass
5415
5416
5417class ArrayAgg(AggFunc):
5418    arg_types = {"this": True, "nulls_excluded": False}
5419
5420
5421class ArrayUniqueAgg(AggFunc):
5422    pass
5423
5424
5425class ArrayAll(Func):
5426    arg_types = {"this": True, "expression": True}
5427
5428
5429# Represents Python's `any(f(x) for x in array)`, where `array` is `this` and `f` is `expression`
5430class ArrayAny(Func):
5431    arg_types = {"this": True, "expression": True}
5432
5433
5434class ArrayConcat(Func):
5435    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
5436    arg_types = {"this": True, "expressions": False}
5437    is_var_len_args = True
5438
5439
5440class ArrayConstructCompact(Func):
5441    arg_types = {"expressions": True}
5442    is_var_len_args = True
5443
5444
5445class ArrayContains(Binary, Func):
5446    _sql_names = ["ARRAY_CONTAINS", "ARRAY_HAS"]
5447
5448
5449class ArrayContainsAll(Binary, Func):
5450    _sql_names = ["ARRAY_CONTAINS_ALL", "ARRAY_HAS_ALL"]
5451
5452
5453class ArrayFilter(Func):
5454    arg_types = {"this": True, "expression": True}
5455    _sql_names = ["FILTER", "ARRAY_FILTER"]
5456
5457
5458class ArrayToString(Func):
5459    arg_types = {"this": True, "expression": True, "null": False}
5460    _sql_names = ["ARRAY_TO_STRING", "ARRAY_JOIN"]
5461
5462
5463# https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#string
5464class String(Func):
5465    arg_types = {"this": True, "zone": False}
5466
5467
5468class StringToArray(Func):
5469    arg_types = {"this": True, "expression": True, "null": False}
5470    _sql_names = ["STRING_TO_ARRAY", "SPLIT_BY_STRING"]
5471
5472
5473class ArrayOverlaps(Binary, Func):
5474    pass
5475
5476
5477class ArraySize(Func):
5478    arg_types = {"this": True, "expression": False}
5479    _sql_names = ["ARRAY_SIZE", "ARRAY_LENGTH"]
5480
5481
5482class ArraySort(Func):
5483    arg_types = {"this": True, "expression": False}
5484
5485
5486class ArraySum(Func):
5487    arg_types = {"this": True, "expression": False}
5488
5489
5490class ArrayUnionAgg(AggFunc):
5491    pass
5492
5493
5494class Avg(AggFunc):
5495    pass
5496
5497
5498class AnyValue(AggFunc):
5499    pass
5500
5501
5502class Lag(AggFunc):
5503    arg_types = {"this": True, "offset": False, "default": False}
5504
5505
5506class Lead(AggFunc):
5507    arg_types = {"this": True, "offset": False, "default": False}
5508
5509
5510# some dialects have a distinction between first and first_value, usually first is an aggregate func
5511# and first_value is a window func
5512class First(AggFunc):
5513    pass
5514
5515
5516class Last(AggFunc):
5517    pass
5518
5519
5520class FirstValue(AggFunc):
5521    pass
5522
5523
5524class LastValue(AggFunc):
5525    pass
5526
5527
5528class NthValue(AggFunc):
5529    arg_types = {"this": True, "offset": True}
5530
5531
5532class Case(Func):
5533    arg_types = {"this": False, "ifs": True, "default": False}
5534
5535    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
5536        instance = maybe_copy(self, copy)
5537        instance.append(
5538            "ifs",
5539            If(
5540                this=maybe_parse(condition, copy=copy, **opts),
5541                true=maybe_parse(then, copy=copy, **opts),
5542            ),
5543        )
5544        return instance
5545
5546    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
5547        instance = maybe_copy(self, copy)
5548        instance.set("default", maybe_parse(condition, copy=copy, **opts))
5549        return instance
5550
5551
5552class Cast(Func):
5553    arg_types = {
5554        "this": True,
5555        "to": True,
5556        "format": False,
5557        "safe": False,
5558        "action": False,
5559        "default": False,
5560    }
5561
5562    @property
5563    def name(self) -> str:
5564        return self.this.name
5565
5566    @property
5567    def to(self) -> DataType:
5568        return self.args["to"]
5569
5570    @property
5571    def output_name(self) -> str:
5572        return self.name
5573
5574    def is_type(self, *dtypes: DATA_TYPE) -> bool:
5575        """
5576        Checks whether this Cast's DataType matches one of the provided data types. Nested types
5577        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
5578        array<int> != array<float>.
5579
5580        Args:
5581            dtypes: the data types to compare this Cast's DataType to.
5582
5583        Returns:
5584            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
5585        """
5586        return self.to.is_type(*dtypes)
5587
5588
5589class TryCast(Cast):
5590    pass
5591
5592
5593# https://clickhouse.com/docs/sql-reference/data-types/newjson#reading-json-paths-as-sub-columns
5594class JSONCast(Cast):
5595    pass
5596
5597
5598class Try(Func):
5599    pass
5600
5601
5602class CastToStrType(Func):
5603    arg_types = {"this": True, "to": True}
5604
5605
5606class Collate(Binary, Func):
5607    pass
5608
5609
5610class Ceil(Func):
5611    arg_types = {"this": True, "decimals": False, "to": False}
5612    _sql_names = ["CEIL", "CEILING"]
5613
5614
5615class Coalesce(Func):
5616    arg_types = {"this": True, "expressions": False, "is_nvl": False}
5617    is_var_len_args = True
5618    _sql_names = ["COALESCE", "IFNULL", "NVL"]
5619
5620
5621class Chr(Func):
5622    arg_types = {"expressions": True, "charset": False}
5623    is_var_len_args = True
5624    _sql_names = ["CHR", "CHAR"]
5625
5626
5627class Concat(Func):
5628    arg_types = {"expressions": True, "safe": False, "coalesce": False}
5629    is_var_len_args = True
5630
5631
5632class ConcatWs(Concat):
5633    _sql_names = ["CONCAT_WS"]
5634
5635
5636class Contains(Func):
5637    arg_types = {"this": True, "expression": True}
5638
5639
5640# https://docs.oracle.com/cd/B13789_01/server.101/b10759/operators004.htm#i1035022
5641class ConnectByRoot(Func):
5642    pass
5643
5644
5645class Count(AggFunc):
5646    arg_types = {"this": False, "expressions": False, "big_int": False}
5647    is_var_len_args = True
5648
5649
5650class CountIf(AggFunc):
5651    _sql_names = ["COUNT_IF", "COUNTIF"]
5652
5653
5654# cube root
5655class Cbrt(Func):
5656    pass
5657
5658
5659class CurrentDate(Func):
5660    arg_types = {"this": False}
5661
5662
5663class CurrentDatetime(Func):
5664    arg_types = {"this": False}
5665
5666
5667class CurrentTime(Func):
5668    arg_types = {"this": False}
5669
5670
5671class CurrentTimestamp(Func):
5672    arg_types = {"this": False, "sysdate": False}
5673
5674
5675class CurrentSchema(Func):
5676    arg_types = {"this": False}
5677
5678
5679class CurrentUser(Func):
5680    arg_types = {"this": False}
5681
5682
5683class DateAdd(Func, IntervalOp):
5684    arg_types = {"this": True, "expression": True, "unit": False}
5685
5686
5687class DateBin(Func, IntervalOp):
5688    arg_types = {"this": True, "expression": True, "unit": False, "zone": False}
5689
5690
5691class DateSub(Func, IntervalOp):
5692    arg_types = {"this": True, "expression": True, "unit": False}
5693
5694
5695class DateDiff(Func, TimeUnit):
5696    _sql_names = ["DATEDIFF", "DATE_DIFF"]
5697    arg_types = {"this": True, "expression": True, "unit": False}
5698
5699
5700class DateTrunc(Func):
5701    arg_types = {"unit": True, "this": True, "zone": False}
5702
5703    def __init__(self, **args):
5704        # Across most dialects it's safe to unabbreviate the unit (e.g. 'Q' -> 'QUARTER') except Oracle
5705        # https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/ROUND-and-TRUNC-Date-Functions.html
5706        unabbreviate = args.pop("unabbreviate", True)
5707
5708        unit = args.get("unit")
5709        if isinstance(unit, TimeUnit.VAR_LIKE):
5710            unit_name = unit.name.upper()
5711            if unabbreviate and unit_name in TimeUnit.UNABBREVIATED_UNIT_NAME:
5712                unit_name = TimeUnit.UNABBREVIATED_UNIT_NAME[unit_name]
5713
5714            args["unit"] = Literal.string(unit_name)
5715        elif isinstance(unit, Week):
5716            unit.set("this", Literal.string(unit.this.name.upper()))
5717
5718        super().__init__(**args)
5719
5720    @property
5721    def unit(self) -> Expression:
5722        return self.args["unit"]
5723
5724
5725# https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions#datetime
5726# expression can either be time_expr or time_zone
5727class Datetime(Func):
5728    arg_types = {"this": True, "expression": False}
5729
5730
5731class DatetimeAdd(Func, IntervalOp):
5732    arg_types = {"this": True, "expression": True, "unit": False}
5733
5734
5735class DatetimeSub(Func, IntervalOp):
5736    arg_types = {"this": True, "expression": True, "unit": False}
5737
5738
5739class DatetimeDiff(Func, TimeUnit):
5740    arg_types = {"this": True, "expression": True, "unit": False}
5741
5742
5743class DatetimeTrunc(Func, TimeUnit):
5744    arg_types = {"this": True, "unit": True, "zone": False}
5745
5746
5747class DayOfWeek(Func):
5748    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
5749
5750
5751# https://duckdb.org/docs/sql/functions/datepart.html#part-specifiers-only-usable-as-date-part-specifiers
5752# ISO day of week function in duckdb is ISODOW
5753class DayOfWeekIso(Func):
5754    _sql_names = ["DAYOFWEEK_ISO", "ISODOW"]
5755
5756
5757class DayOfMonth(Func):
5758    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
5759
5760
5761class DayOfYear(Func):
5762    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
5763
5764
5765class ToDays(Func):
5766    pass
5767
5768
5769class WeekOfYear(Func):
5770    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
5771
5772
5773class MonthsBetween(Func):
5774    arg_types = {"this": True, "expression": True, "roundoff": False}
5775
5776
5777class MakeInterval(Func):
5778    arg_types = {
5779        "year": False,
5780        "month": False,
5781        "day": False,
5782        "hour": False,
5783        "minute": False,
5784        "second": False,
5785    }
5786
5787
5788class LastDay(Func, TimeUnit):
5789    _sql_names = ["LAST_DAY", "LAST_DAY_OF_MONTH"]
5790    arg_types = {"this": True, "unit": False}
5791
5792
5793class Extract(Func):
5794    arg_types = {"this": True, "expression": True}
5795
5796
5797class Exists(Func, SubqueryPredicate):
5798    arg_types = {"this": True, "expression": False}
5799
5800
5801class Timestamp(Func):
5802    arg_types = {"this": False, "zone": False, "with_tz": False}
5803
5804
5805class TimestampAdd(Func, TimeUnit):
5806    arg_types = {"this": True, "expression": True, "unit": False}
5807
5808
5809class TimestampSub(Func, TimeUnit):
5810    arg_types = {"this": True, "expression": True, "unit": False}
5811
5812
5813class TimestampDiff(Func, TimeUnit):
5814    _sql_names = ["TIMESTAMPDIFF", "TIMESTAMP_DIFF"]
5815    arg_types = {"this": True, "expression": True, "unit": False}
5816
5817
5818class TimestampTrunc(Func, TimeUnit):
5819    arg_types = {"this": True, "unit": True, "zone": False}
5820
5821
5822class TimeAdd(Func, TimeUnit):
5823    arg_types = {"this": True, "expression": True, "unit": False}
5824
5825
5826class TimeSub(Func, TimeUnit):
5827    arg_types = {"this": True, "expression": True, "unit": False}
5828
5829
5830class TimeDiff(Func, TimeUnit):
5831    arg_types = {"this": True, "expression": True, "unit": False}
5832
5833
5834class TimeTrunc(Func, TimeUnit):
5835    arg_types = {"this": True, "unit": True, "zone": False}
5836
5837
5838class DateFromParts(Func):
5839    _sql_names = ["DATE_FROM_PARTS", "DATEFROMPARTS"]
5840    arg_types = {"year": True, "month": True, "day": True}
5841
5842
5843class TimeFromParts(Func):
5844    _sql_names = ["TIME_FROM_PARTS", "TIMEFROMPARTS"]
5845    arg_types = {
5846        "hour": True,
5847        "min": True,
5848        "sec": True,
5849        "nano": False,
5850        "fractions": False,
5851        "precision": False,
5852    }
5853
5854
5855class DateStrToDate(Func):
5856    pass
5857
5858
5859class DateToDateStr(Func):
5860    pass
5861
5862
5863class DateToDi(Func):
5864    pass
5865
5866
5867# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
5868class Date(Func):
5869    arg_types = {"this": False, "zone": False, "expressions": False}
5870    is_var_len_args = True
5871
5872
5873class Day(Func):
5874    pass
5875
5876
5877class Decode(Func):
5878    arg_types = {"this": True, "charset": True, "replace": False}
5879
5880
5881class DiToDate(Func):
5882    pass
5883
5884
5885class Encode(Func):
5886    arg_types = {"this": True, "charset": True}
5887
5888
5889class Exp(Func):
5890    pass
5891
5892
5893# https://docs.snowflake.com/en/sql-reference/functions/flatten
5894class Explode(Func, UDTF):
5895    arg_types = {"this": True, "expressions": False}
5896    is_var_len_args = True
5897
5898
5899# https://spark.apache.org/docs/latest/api/sql/#inline
5900class Inline(Func):
5901    pass
5902
5903
5904class ExplodeOuter(Explode):
5905    pass
5906
5907
5908class Posexplode(Explode):
5909    pass
5910
5911
5912class PosexplodeOuter(Posexplode, ExplodeOuter):
5913    pass
5914
5915
5916class Unnest(Func, UDTF):
5917    arg_types = {
5918        "expressions": True,
5919        "alias": False,
5920        "offset": False,
5921        "explode_array": False,
5922    }
5923
5924    @property
5925    def selects(self) -> t.List[Expression]:
5926        columns = super().selects
5927        offset = self.args.get("offset")
5928        if offset:
5929            columns = columns + [to_identifier("offset") if offset is True else offset]
5930        return columns
5931
5932
5933class Floor(Func):
5934    arg_types = {"this": True, "decimals": False, "to": False}
5935
5936
5937class FromBase64(Func):
5938    pass
5939
5940
5941class FeaturesAtTime(Func):
5942    arg_types = {"this": True, "time": False, "num_rows": False, "ignore_feature_nulls": False}
5943
5944
5945class ToBase64(Func):
5946    pass
5947
5948
5949# https://trino.io/docs/current/functions/datetime.html#from_iso8601_timestamp
5950class FromISO8601Timestamp(Func):
5951    _sql_names = ["FROM_ISO8601_TIMESTAMP"]
5952
5953
5954class GapFill(Func):
5955    arg_types = {
5956        "this": True,
5957        "ts_column": True,
5958        "bucket_width": True,
5959        "partitioning_columns": False,
5960        "value_columns": False,
5961        "origin": False,
5962        "ignore_nulls": False,
5963    }
5964
5965
5966# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_date_array
5967class GenerateDateArray(Func):
5968    arg_types = {"start": True, "end": True, "step": False}
5969
5970
5971# https://cloud.google.com/bigquery/docs/reference/standard-sql/array_functions#generate_timestamp_array
5972class GenerateTimestampArray(Func):
5973    arg_types = {"start": True, "end": True, "step": True}
5974
5975
5976class Greatest(Func):
5977    arg_types = {"this": True, "expressions": False}
5978    is_var_len_args = True
5979
5980
5981# Trino's `ON OVERFLOW TRUNCATE [filler_string] {WITH | WITHOUT} COUNT`
5982# https://trino.io/docs/current/functions/aggregate.html#listagg
5983class OverflowTruncateBehavior(Expression):
5984    arg_types = {"this": False, "with_count": True}
5985
5986
5987class GroupConcat(AggFunc):
5988    arg_types = {"this": True, "separator": False, "on_overflow": False}
5989
5990
5991class Hex(Func):
5992    pass
5993
5994
5995class LowerHex(Hex):
5996    pass
5997
5998
5999class And(Connector, Func):
6000    pass
6001
6002
6003class Or(Connector, Func):
6004    pass
6005
6006
6007class Xor(Connector, Func):
6008    arg_types = {"this": False, "expression": False, "expressions": False}
6009
6010
6011class If(Func):
6012    arg_types = {"this": True, "true": True, "false": False}
6013    _sql_names = ["IF", "IIF"]
6014
6015
6016class Nullif(Func):
6017    arg_types = {"this": True, "expression": True}
6018
6019
6020class Initcap(Func):
6021    arg_types = {"this": True, "expression": False}
6022
6023
6024class IsAscii(Func):
6025    pass
6026
6027
6028class IsNan(Func):
6029    _sql_names = ["IS_NAN", "ISNAN"]
6030
6031
6032# https://cloud.google.com/bigquery/docs/reference/standard-sql/json_functions#int64_for_json
6033class Int64(Func):
6034    pass
6035
6036
6037class IsInf(Func):
6038    _sql_names = ["IS_INF", "ISINF"]
6039
6040
6041# https://www.postgresql.org/docs/current/functions-json.html
6042class JSON(Expression):
6043    arg_types = {"this": False, "with": False, "unique": False}
6044
6045
6046class JSONPath(Expression):
6047    arg_types = {"expressions": True, "escape": False}
6048
6049    @property
6050    def output_name(self) -> str:
6051        last_segment = self.expressions[-1].this
6052        return last_segment if isinstance(last_segment, str) else ""
6053
6054
6055class JSONPathPart(Expression):
6056    arg_types = {}
6057
6058
6059class JSONPathFilter(JSONPathPart):
6060    arg_types = {"this": True}
6061
6062
6063class JSONPathKey(JSONPathPart):
6064    arg_types = {"this": True}
6065
6066
6067class JSONPathRecursive(JSONPathPart):
6068    arg_types = {"this": False}
6069
6070
6071class JSONPathRoot(JSONPathPart):
6072    pass
6073
6074
6075class JSONPathScript(JSONPathPart):
6076    arg_types = {"this": True}
6077
6078
6079class JSONPathSlice(JSONPathPart):
6080    arg_types = {"start": False, "end": False, "step": False}
6081
6082
6083class JSONPathSelector(JSONPathPart):
6084    arg_types = {"this": True}
6085
6086
6087class JSONPathSubscript(JSONPathPart):
6088    arg_types = {"this": True}
6089
6090
6091class JSONPathUnion(JSONPathPart):
6092    arg_types = {"expressions": True}
6093
6094
6095class JSONPathWildcard(JSONPathPart):
6096    pass
6097
6098
6099class FormatJson(Expression):
6100    pass
6101
6102
6103class JSONKeyValue(Expression):
6104    arg_types = {"this": True, "expression": True}
6105
6106
6107class JSONObject(Func):
6108    arg_types = {
6109        "expressions": False,
6110        "null_handling": False,
6111        "unique_keys": False,
6112        "return_type": False,
6113        "encoding": False,
6114    }
6115
6116
6117class JSONObjectAgg(AggFunc):
6118    arg_types = {
6119        "expressions": False,
6120        "null_handling": False,
6121        "unique_keys": False,
6122        "return_type": False,
6123        "encoding": False,
6124    }
6125
6126
6127# https://www.postgresql.org/docs/9.5/functions-aggregate.html
6128class JSONBObjectAgg(AggFunc):
6129    arg_types = {"this": True, "expression": True}
6130
6131
6132# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
6133class JSONArray(Func):
6134    arg_types = {
6135        "expressions": True,
6136        "null_handling": False,
6137        "return_type": False,
6138        "strict": False,
6139    }
6140
6141
6142# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
6143class JSONArrayAgg(Func):
6144    arg_types = {
6145        "this": True,
6146        "order": False,
6147        "null_handling": False,
6148        "return_type": False,
6149        "strict": False,
6150    }
6151
6152
6153class JSONExists(Func):
6154    arg_types = {"this": True, "path": True, "passing": False, "on_condition": False}
6155
6156
6157# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
6158# Note: parsing of JSON column definitions is currently incomplete.
6159class JSONColumnDef(Expression):
6160    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
6161
6162
6163class JSONSchema(Expression):
6164    arg_types = {"expressions": True}
6165
6166
6167# https://dev.mysql.com/doc/refman/8.4/en/json-search-functions.html#function_json-value
6168class JSONValue(Expression):
6169    arg_types = {
6170        "this": True,
6171        "path": True,
6172        "returning": False,
6173        "on_condition": False,
6174    }
6175
6176
6177class JSONValueArray(Func):
6178    arg_types = {"this": True, "expression": False}
6179
6180
6181# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
6182class JSONTable(Func):
6183    arg_types = {
6184        "this": True,
6185        "schema": True,
6186        "path": False,
6187        "error_handling": False,
6188        "empty_handling": False,
6189    }
6190
6191
6192# https://docs.snowflake.com/en/sql-reference/functions/object_insert
6193class ObjectInsert(Func):
6194    arg_types = {
6195        "this": True,
6196        "key": True,
6197        "value": True,
6198        "update_flag": False,
6199    }
6200
6201
6202class OpenJSONColumnDef(Expression):
6203    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
6204
6205
6206class OpenJSON(Func):
6207    arg_types = {"this": True, "path": False, "expressions": False}
6208
6209
6210class JSONBContains(Binary, Func):
6211    _sql_names = ["JSONB_CONTAINS"]
6212
6213
6214class JSONBExists(Func):
6215    arg_types = {"this": True, "path": True}
6216    _sql_names = ["JSONB_EXISTS"]
6217
6218
6219class JSONExtract(Binary, Func):
6220    arg_types = {
6221        "this": True,
6222        "expression": True,
6223        "only_json_types": False,
6224        "expressions": False,
6225        "variant_extract": False,
6226        "json_query": False,
6227        "option": False,
6228        "quote": False,
6229        "on_condition": False,
6230    }
6231    _sql_names = ["JSON_EXTRACT"]
6232    is_var_len_args = True
6233
6234    @property
6235    def output_name(self) -> str:
6236        return self.expression.output_name if not self.expressions else ""
6237
6238
6239# https://trino.io/docs/current/functions/json.html#json-query
6240class JSONExtractQuote(Expression):
6241    arg_types = {
6242        "option": True,
6243        "scalar": False,
6244    }
6245
6246
6247class JSONExtractArray(Func):
6248    arg_types = {"this": True, "expression": False}
6249    _sql_names = ["JSON_EXTRACT_ARRAY"]
6250
6251
6252class JSONExtractScalar(Binary, Func):
6253    arg_types = {"this": True, "expression": True, "only_json_types": False, "expressions": False}
6254    _sql_names = ["JSON_EXTRACT_SCALAR"]
6255    is_var_len_args = True
6256
6257    @property
6258    def output_name(self) -> str:
6259        return self.expression.output_name
6260
6261
6262class JSONBExtract(Binary, Func):
6263    _sql_names = ["JSONB_EXTRACT"]
6264
6265
6266class JSONBExtractScalar(Binary, Func):
6267    _sql_names = ["JSONB_EXTRACT_SCALAR"]
6268
6269
6270class JSONFormat(Func):
6271    arg_types = {"this": False, "options": False}
6272    _sql_names = ["JSON_FORMAT"]
6273
6274
6275# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
6276class JSONArrayContains(Binary, Predicate, Func):
6277    _sql_names = ["JSON_ARRAY_CONTAINS"]
6278
6279
6280class ParseJSON(Func):
6281    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
6282    # Snowflake also has TRY_PARSE_JSON, which is represented using `safe`
6283    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
6284    arg_types = {"this": True, "expression": False, "safe": False}
6285
6286
6287class Least(Func):
6288    arg_types = {"this": True, "expressions": False}
6289    is_var_len_args = True
6290
6291
6292class Left(Func):
6293    arg_types = {"this": True, "expression": True}
6294
6295
6296class Right(Func):
6297    arg_types = {"this": True, "expression": True}
6298
6299
6300class Length(Func):
6301    arg_types = {"this": True, "binary": False, "encoding": False}
6302    _sql_names = ["LENGTH", "LEN", "CHAR_LENGTH", "CHARACTER_LENGTH"]
6303
6304
6305class Levenshtein(Func):
6306    arg_types = {
6307        "this": True,
6308        "expression": False,
6309        "ins_cost": False,
6310        "del_cost": False,
6311        "sub_cost": False,
6312        "max_dist": False,
6313    }
6314
6315
6316class Ln(Func):
6317    pass
6318
6319
6320class Log(Func):
6321    arg_types = {"this": True, "expression": False}
6322
6323
6324class LogicalOr(AggFunc):
6325    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
6326
6327
6328class LogicalAnd(AggFunc):
6329    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
6330
6331
6332class Lower(Func):
6333    _sql_names = ["LOWER", "LCASE"]
6334
6335
6336class Map(Func):
6337    arg_types = {"keys": False, "values": False}
6338
6339    @property
6340    def keys(self) -> t.List[Expression]:
6341        keys = self.args.get("keys")
6342        return keys.expressions if keys else []
6343
6344    @property
6345    def values(self) -> t.List[Expression]:
6346        values = self.args.get("values")
6347        return values.expressions if values else []
6348
6349
6350# Represents the MAP {...} syntax in DuckDB - basically convert a struct to a MAP
6351class ToMap(Func):
6352    pass
6353
6354
6355class MapFromEntries(Func):
6356    pass
6357
6358
6359# https://learn.microsoft.com/en-us/sql/t-sql/language-elements/scope-resolution-operator-transact-sql?view=sql-server-ver16
6360class ScopeResolution(Expression):
6361    arg_types = {"this": False, "expression": True}
6362
6363
6364class Stream(Expression):
6365    pass
6366
6367
6368class StarMap(Func):
6369    pass
6370
6371
6372class VarMap(Func):
6373    arg_types = {"keys": True, "values": True}
6374    is_var_len_args = True
6375
6376    @property
6377    def keys(self) -> t.List[Expression]:
6378        return self.args["keys"].expressions
6379
6380    @property
6381    def values(self) -> t.List[Expression]:
6382        return self.args["values"].expressions
6383
6384
6385# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
6386class MatchAgainst(Func):
6387    arg_types = {"this": True, "expressions": True, "modifier": False}
6388
6389
6390class Max(AggFunc):
6391    arg_types = {"this": True, "expressions": False}
6392    is_var_len_args = True
6393
6394
6395class MD5(Func):
6396    _sql_names = ["MD5"]
6397
6398
6399# Represents the variant of the MD5 function that returns a binary value
6400class MD5Digest(Func):
6401    _sql_names = ["MD5_DIGEST"]
6402
6403
6404class Median(AggFunc):
6405    pass
6406
6407
6408class Min(AggFunc):
6409    arg_types = {"this": True, "expressions": False}
6410    is_var_len_args = True
6411
6412
6413class Month(Func):
6414    pass
6415
6416
6417class AddMonths(Func):
6418    arg_types = {"this": True, "expression": True}
6419
6420
6421class Nvl2(Func):
6422    arg_types = {"this": True, "true": True, "false": False}
6423
6424
6425class Normalize(Func):
6426    arg_types = {"this": True, "form": False}
6427
6428
6429class Overlay(Func):
6430    arg_types = {"this": True, "expression": True, "from": True, "for": False}
6431
6432
6433# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
6434class Predict(Func):
6435    arg_types = {"this": True, "expression": True, "params_struct": False}
6436
6437
6438class Pow(Binary, Func):
6439    _sql_names = ["POWER", "POW"]
6440
6441
6442class PercentileCont(AggFunc):
6443    arg_types = {"this": True, "expression": False}
6444
6445
6446class PercentileDisc(AggFunc):
6447    arg_types = {"this": True, "expression": False}
6448
6449
6450class Quantile(AggFunc):
6451    arg_types = {"this": True, "quantile": True}
6452
6453
6454class ApproxQuantile(Quantile):
6455    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
6456
6457
6458class Quarter(Func):
6459    pass
6460
6461
6462# https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/SQL-Functions-Expressions-and-Predicates/Arithmetic-Trigonometric-Hyperbolic-Operators/Functions/RANDOM/RANDOM-Function-Syntax
6463# teradata lower and upper bounds
6464class Rand(Func):
6465    _sql_names = ["RAND", "RANDOM"]
6466    arg_types = {"this": False, "lower": False, "upper": False}
6467
6468
6469class Randn(Func):
6470    arg_types = {"this": False}
6471
6472
6473class RangeN(Func):
6474    arg_types = {"this": True, "expressions": True, "each": False}
6475
6476
6477class ReadCSV(Func):
6478    _sql_names = ["READ_CSV"]
6479    is_var_len_args = True
6480    arg_types = {"this": True, "expressions": False}
6481
6482
6483class Reduce(Func):
6484    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
6485
6486
6487class RegexpExtract(Func):
6488    arg_types = {
6489        "this": True,
6490        "expression": True,
6491        "position": False,
6492        "occurrence": False,
6493        "parameters": False,
6494        "group": False,
6495    }
6496
6497
6498class RegexpExtractAll(Func):
6499    arg_types = {
6500        "this": True,
6501        "expression": True,
6502        "position": False,
6503        "occurrence": False,
6504        "parameters": False,
6505        "group": False,
6506    }
6507
6508
6509class RegexpReplace(Func):
6510    arg_types = {
6511        "this": True,
6512        "expression": True,
6513        "replacement": False,
6514        "position": False,
6515        "occurrence": False,
6516        "modifiers": False,
6517    }
6518
6519
6520class RegexpLike(Binary, Func):
6521    arg_types = {"this": True, "expression": True, "flag": False}
6522
6523
6524class RegexpILike(Binary, Func):
6525    arg_types = {"this": True, "expression": True, "flag": False}
6526
6527
6528# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
6529# limit is the number of times a pattern is applied
6530class RegexpSplit(Func):
6531    arg_types = {"this": True, "expression": True, "limit": False}
6532
6533
6534class Repeat(Func):
6535    arg_types = {"this": True, "times": True}
6536
6537
6538# https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-ver16
6539# tsql third argument function == trunctaion if not 0
6540class Round(Func):
6541    arg_types = {"this": True, "decimals": False, "truncate": False}
6542
6543
6544class RowNumber(Func):
6545    arg_types = {"this": False}
6546
6547
6548class SafeDivide(Func):
6549    arg_types = {"this": True, "expression": True}
6550
6551
6552class SHA(Func):
6553    _sql_names = ["SHA", "SHA1"]
6554
6555
6556class SHA2(Func):
6557    _sql_names = ["SHA2"]
6558    arg_types = {"this": True, "length": False}
6559
6560
6561class Sign(Func):
6562    _sql_names = ["SIGN", "SIGNUM"]
6563
6564
6565class SortArray(Func):
6566    arg_types = {"this": True, "asc": False}
6567
6568
6569class Split(Func):
6570    arg_types = {"this": True, "expression": True, "limit": False}
6571
6572
6573# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split_part.html
6574class SplitPart(Func):
6575    arg_types = {"this": True, "delimiter": True, "part_index": True}
6576
6577
6578# Start may be omitted in the case of postgres
6579# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
6580class Substring(Func):
6581    _sql_names = ["SUBSTRING", "SUBSTR"]
6582    arg_types = {"this": True, "start": False, "length": False}
6583
6584
6585class StandardHash(Func):
6586    arg_types = {"this": True, "expression": False}
6587
6588
6589class StartsWith(Func):
6590    _sql_names = ["STARTS_WITH", "STARTSWITH"]
6591    arg_types = {"this": True, "expression": True}
6592
6593
6594class StrPosition(Func):
6595    arg_types = {
6596        "this": True,
6597        "substr": True,
6598        "position": False,
6599        "occurrence": False,
6600    }
6601
6602
6603class StrToDate(Func):
6604    arg_types = {"this": True, "format": False, "safe": False}
6605
6606
6607class StrToTime(Func):
6608    arg_types = {"this": True, "format": True, "zone": False, "safe": False}
6609
6610
6611# Spark allows unix_timestamp()
6612# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
6613class StrToUnix(Func):
6614    arg_types = {"this": False, "format": False}
6615
6616
6617# https://prestodb.io/docs/current/functions/string.html
6618# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
6619class StrToMap(Func):
6620    arg_types = {
6621        "this": True,
6622        "pair_delim": False,
6623        "key_value_delim": False,
6624        "duplicate_resolution_callback": False,
6625    }
6626
6627
6628class NumberToStr(Func):
6629    arg_types = {"this": True, "format": True, "culture": False}
6630
6631
6632class FromBase(Func):
6633    arg_types = {"this": True, "expression": True}
6634
6635
6636class Struct(Func):
6637    arg_types = {"expressions": False}
6638    is_var_len_args = True
6639
6640
6641class StructExtract(Func):
6642    arg_types = {"this": True, "expression": True}
6643
6644
6645# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
6646# https://docs.snowflake.com/en/sql-reference/functions/insert
6647class Stuff(Func):
6648    _sql_names = ["STUFF", "INSERT"]
6649    arg_types = {"this": True, "start": True, "length": True, "expression": True}
6650
6651
6652class Sum(AggFunc):
6653    pass
6654
6655
6656class Sqrt(Func):
6657    pass
6658
6659
6660class Stddev(AggFunc):
6661    _sql_names = ["STDDEV", "STDEV"]
6662
6663
6664class StddevPop(AggFunc):
6665    pass
6666
6667
6668class StddevSamp(AggFunc):
6669    pass
6670
6671
6672# https://cloud.google.com/bigquery/docs/reference/standard-sql/time_functions#time
6673class Time(Func):
6674    arg_types = {"this": False, "zone": False}
6675
6676
6677class TimeToStr(Func):
6678    arg_types = {"this": True, "format": True, "culture": False, "zone": False}
6679
6680
6681class TimeToTimeStr(Func):
6682    pass
6683
6684
6685class TimeToUnix(Func):
6686    pass
6687
6688
6689class TimeStrToDate(Func):
6690    pass
6691
6692
6693class TimeStrToTime(Func):
6694    arg_types = {"this": True, "zone": False}
6695
6696
6697class TimeStrToUnix(Func):
6698    pass
6699
6700
6701class Trim(Func):
6702    arg_types = {
6703        "this": True,
6704        "expression": False,
6705        "position": False,
6706        "collation": False,
6707    }
6708
6709
6710class TsOrDsAdd(Func, TimeUnit):
6711    # return_type is used to correctly cast the arguments of this expression when transpiling it
6712    arg_types = {"this": True, "expression": True, "unit": False, "return_type": False}
6713
6714    @property
6715    def return_type(self) -> DataType:
6716        return DataType.build(self.args.get("return_type") or DataType.Type.DATE)
6717
6718
6719class TsOrDsDiff(Func, TimeUnit):
6720    arg_types = {"this": True, "expression": True, "unit": False}
6721
6722
6723class TsOrDsToDateStr(Func):
6724    pass
6725
6726
6727class TsOrDsToDate(Func):
6728    arg_types = {"this": True, "format": False, "safe": False}
6729
6730
6731class TsOrDsToDatetime(Func):
6732    pass
6733
6734
6735class TsOrDsToTime(Func):
6736    arg_types = {"this": True, "format": False, "safe": False}
6737
6738
6739class TsOrDsToTimestamp(Func):
6740    pass
6741
6742
6743class TsOrDiToDi(Func):
6744    pass
6745
6746
6747class Unhex(Func):
6748    arg_types = {"this": True, "expression": False}
6749
6750
6751class Unicode(Func):
6752    pass
6753
6754
6755# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#unix_date
6756class UnixDate(Func):
6757    pass
6758
6759
6760class UnixToStr(Func):
6761    arg_types = {"this": True, "format": False}
6762
6763
6764# https://prestodb.io/docs/current/functions/datetime.html
6765# presto has weird zone/hours/minutes
6766class UnixToTime(Func):
6767    arg_types = {
6768        "this": True,
6769        "scale": False,
6770        "zone": False,
6771        "hours": False,
6772        "minutes": False,
6773        "format": False,
6774    }
6775
6776    SECONDS = Literal.number(0)
6777    DECIS = Literal.number(1)
6778    CENTIS = Literal.number(2)
6779    MILLIS = Literal.number(3)
6780    DECIMILLIS = Literal.number(4)
6781    CENTIMILLIS = Literal.number(5)
6782    MICROS = Literal.number(6)
6783    DECIMICROS = Literal.number(7)
6784    CENTIMICROS = Literal.number(8)
6785    NANOS = Literal.number(9)
6786
6787
6788class UnixToTimeStr(Func):
6789    pass
6790
6791
6792class UnixSeconds(Func):
6793    pass
6794
6795
6796class Uuid(Func):
6797    _sql_names = ["UUID", "GEN_RANDOM_UUID", "GENERATE_UUID", "UUID_STRING"]
6798
6799    arg_types = {"this": False, "name": False}
6800
6801
6802class TimestampFromParts(Func):
6803    _sql_names = ["TIMESTAMP_FROM_PARTS", "TIMESTAMPFROMPARTS"]
6804    arg_types = {
6805        "year": True,
6806        "month": True,
6807        "day": True,
6808        "hour": True,
6809        "min": True,
6810        "sec": True,
6811        "nano": False,
6812        "zone": False,
6813        "milli": False,
6814    }
6815
6816
6817class Upper(Func):
6818    _sql_names = ["UPPER", "UCASE"]
6819
6820
6821class Corr(Binary, AggFunc):
6822    pass
6823
6824
6825class Variance(AggFunc):
6826    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
6827
6828
6829class VariancePop(AggFunc):
6830    _sql_names = ["VARIANCE_POP", "VAR_POP"]
6831
6832
6833class CovarSamp(Binary, AggFunc):
6834    pass
6835
6836
6837class CovarPop(Binary, AggFunc):
6838    pass
6839
6840
6841class Week(Func):
6842    arg_types = {"this": True, "mode": False}
6843
6844
6845class XMLElement(Func):
6846    _sql_names = ["XMLELEMENT"]
6847    arg_types = {"this": True, "expressions": False}
6848
6849
6850class XMLTable(Func):
6851    arg_types = {
6852        "this": True,
6853        "namespaces": False,
6854        "passing": False,
6855        "columns": False,
6856        "by_ref": False,
6857    }
6858
6859
6860class XMLNamespace(Expression):
6861    pass
6862
6863
6864class Year(Func):
6865    pass
6866
6867
6868class Use(Expression):
6869    arg_types = {"this": False, "expressions": False, "kind": False}
6870
6871
6872class Merge(DML):
6873    arg_types = {
6874        "this": True,
6875        "using": True,
6876        "on": True,
6877        "whens": True,
6878        "with": False,
6879        "returning": False,
6880    }
6881
6882
6883class When(Expression):
6884    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
6885
6886
6887class Whens(Expression):
6888    """Wraps around one or more WHEN [NOT] MATCHED [...] clauses."""
6889
6890    arg_types = {"expressions": True}
6891
6892
6893# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
6894# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
6895class NextValueFor(Func):
6896    arg_types = {"this": True, "order": False}
6897
6898
6899# Refers to a trailing semi-colon. This is only used to preserve trailing comments
6900# select 1; -- my comment
6901class Semicolon(Expression):
6902    arg_types = {}
6903
6904
6905def _norm_arg(arg):
6906    return arg.lower() if type(arg) is str else arg
6907
6908
6909ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
6910FUNCTION_BY_NAME = {name: func for func in ALL_FUNCTIONS for name in func.sql_names()}
6911
6912JSON_PATH_PARTS = subclasses(__name__, JSONPathPart, (JSONPathPart,))
6913
6914PERCENTILES = (PercentileCont, PercentileDisc)
6915
6916
6917# Helpers
6918@t.overload
6919def maybe_parse(
6920    sql_or_expression: ExpOrStr,
6921    *,
6922    into: t.Type[E],
6923    dialect: DialectType = None,
6924    prefix: t.Optional[str] = None,
6925    copy: bool = False,
6926    **opts,
6927) -> E: ...
6928
6929
6930@t.overload
6931def maybe_parse(
6932    sql_or_expression: str | E,
6933    *,
6934    into: t.Optional[IntoType] = None,
6935    dialect: DialectType = None,
6936    prefix: t.Optional[str] = None,
6937    copy: bool = False,
6938    **opts,
6939) -> E: ...
6940
6941
6942def maybe_parse(
6943    sql_or_expression: ExpOrStr,
6944    *,
6945    into: t.Optional[IntoType] = None,
6946    dialect: DialectType = None,
6947    prefix: t.Optional[str] = None,
6948    copy: bool = False,
6949    **opts,
6950) -> Expression:
6951    """Gracefully handle a possible string or expression.
6952
6953    Example:
6954        >>> maybe_parse("1")
6955        Literal(this=1, is_string=False)
6956        >>> maybe_parse(to_identifier("x"))
6957        Identifier(this=x, quoted=False)
6958
6959    Args:
6960        sql_or_expression: the SQL code string or an expression
6961        into: the SQLGlot Expression to parse into
6962        dialect: the dialect used to parse the input expressions (in the case that an
6963            input expression is a SQL string).
6964        prefix: a string to prefix the sql with before it gets parsed
6965            (automatically includes a space)
6966        copy: whether to copy the expression.
6967        **opts: other options to use to parse the input expressions (again, in the case
6968            that an input expression is a SQL string).
6969
6970    Returns:
6971        Expression: the parsed or given expression.
6972    """
6973    if isinstance(sql_or_expression, Expression):
6974        if copy:
6975            return sql_or_expression.copy()
6976        return sql_or_expression
6977
6978    if sql_or_expression is None:
6979        raise ParseError("SQL cannot be None")
6980
6981    import sqlglot
6982
6983    sql = str(sql_or_expression)
6984    if prefix:
6985        sql = f"{prefix} {sql}"
6986
6987    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
6988
6989
6990@t.overload
6991def maybe_copy(instance: None, copy: bool = True) -> None: ...
6992
6993
6994@t.overload
6995def maybe_copy(instance: E, copy: bool = True) -> E: ...
6996
6997
6998def maybe_copy(instance, copy=True):
6999    return instance.copy() if copy and instance else instance
7000
7001
7002def _to_s(node: t.Any, verbose: bool = False, level: int = 0) -> str:
7003    """Generate a textual representation of an Expression tree"""
7004    indent = "\n" + ("  " * (level + 1))
7005    delim = f",{indent}"
7006
7007    if isinstance(node, Expression):
7008        args = {k: v for k, v in node.args.items() if (v is not None and v != []) or verbose}
7009
7010        if (node.type or verbose) and not isinstance(node, DataType):
7011            args["_type"] = node.type
7012        if node.comments or verbose:
7013            args["_comments"] = node.comments
7014
7015        if verbose:
7016            args["_id"] = id(node)
7017
7018        # Inline leaves for a more compact representation
7019        if node.is_leaf():
7020            indent = ""
7021            delim = ", "
7022
7023        items = delim.join([f"{k}={_to_s(v, verbose, level + 1)}" for k, v in args.items()])
7024        return f"{node.__class__.__name__}({indent}{items})"
7025
7026    if isinstance(node, list):
7027        items = delim.join(_to_s(i, verbose, level + 1) for i in node)
7028        items = f"{indent}{items}" if items else ""
7029        return f"[{items}]"
7030
7031    # Indent multiline strings to match the current level
7032    return indent.join(textwrap.dedent(str(node).strip("\n")).splitlines())
7033
7034
7035def _is_wrong_expression(expression, into):
7036    return isinstance(expression, Expression) and not isinstance(expression, into)
7037
7038
7039def _apply_builder(
7040    expression,
7041    instance,
7042    arg,
7043    copy=True,
7044    prefix=None,
7045    into=None,
7046    dialect=None,
7047    into_arg="this",
7048    **opts,
7049):
7050    if _is_wrong_expression(expression, into):
7051        expression = into(**{into_arg: expression})
7052    instance = maybe_copy(instance, copy)
7053    expression = maybe_parse(
7054        sql_or_expression=expression,
7055        prefix=prefix,
7056        into=into,
7057        dialect=dialect,
7058        **opts,
7059    )
7060    instance.set(arg, expression)
7061    return instance
7062
7063
7064def _apply_child_list_builder(
7065    *expressions,
7066    instance,
7067    arg,
7068    append=True,
7069    copy=True,
7070    prefix=None,
7071    into=None,
7072    dialect=None,
7073    properties=None,
7074    **opts,
7075):
7076    instance = maybe_copy(instance, copy)
7077    parsed = []
7078    properties = {} if properties is None else properties
7079
7080    for expression in expressions:
7081        if expression is not None:
7082            if _is_wrong_expression(expression, into):
7083                expression = into(expressions=[expression])
7084
7085            expression = maybe_parse(
7086                expression,
7087                into=into,
7088                dialect=dialect,
7089                prefix=prefix,
7090                **opts,
7091            )
7092            for k, v in expression.args.items():
7093                if k == "expressions":
7094                    parsed.extend(v)
7095                else:
7096                    properties[k] = v
7097
7098    existing = instance.args.get(arg)
7099    if append and existing:
7100        parsed = existing.expressions + parsed
7101
7102    child = into(expressions=parsed)
7103    for k, v in properties.items():
7104        child.set(k, v)
7105    instance.set(arg, child)
7106
7107    return instance
7108
7109
7110def _apply_list_builder(
7111    *expressions,
7112    instance,
7113    arg,
7114    append=True,
7115    copy=True,
7116    prefix=None,
7117    into=None,
7118    dialect=None,
7119    **opts,
7120):
7121    inst = maybe_copy(instance, copy)
7122
7123    expressions = [
7124        maybe_parse(
7125            sql_or_expression=expression,
7126            into=into,
7127            prefix=prefix,
7128            dialect=dialect,
7129            **opts,
7130        )
7131        for expression in expressions
7132        if expression is not None
7133    ]
7134
7135    existing_expressions = inst.args.get(arg)
7136    if append and existing_expressions:
7137        expressions = existing_expressions + expressions
7138
7139    inst.set(arg, expressions)
7140    return inst
7141
7142
7143def _apply_conjunction_builder(
7144    *expressions,
7145    instance,
7146    arg,
7147    into=None,
7148    append=True,
7149    copy=True,
7150    dialect=None,
7151    **opts,
7152):
7153    expressions = [exp for exp in expressions if exp is not None and exp != ""]
7154    if not expressions:
7155        return instance
7156
7157    inst = maybe_copy(instance, copy)
7158
7159    existing = inst.args.get(arg)
7160    if append and existing is not None:
7161        expressions = [existing.this if into else existing] + list(expressions)
7162
7163    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
7164
7165    inst.set(arg, into(this=node) if into else node)
7166    return inst
7167
7168
7169def _apply_cte_builder(
7170    instance: E,
7171    alias: ExpOrStr,
7172    as_: ExpOrStr,
7173    recursive: t.Optional[bool] = None,
7174    materialized: t.Optional[bool] = None,
7175    append: bool = True,
7176    dialect: DialectType = None,
7177    copy: bool = True,
7178    scalar: bool = False,
7179    **opts,
7180) -> E:
7181    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
7182    as_expression = maybe_parse(as_, dialect=dialect, copy=copy, **opts)
7183    if scalar and not isinstance(as_expression, Subquery):
7184        # scalar CTE must be wrapped in a subquery
7185        as_expression = Subquery(this=as_expression)
7186    cte = CTE(this=as_expression, alias=alias_expression, materialized=materialized, scalar=scalar)
7187    return _apply_child_list_builder(
7188        cte,
7189        instance=instance,
7190        arg="with",
7191        append=append,
7192        copy=copy,
7193        into=With,
7194        properties={"recursive": recursive or False},
7195    )
7196
7197
7198def _combine(
7199    expressions: t.Sequence[t.Optional[ExpOrStr]],
7200    operator: t.Type[Connector],
7201    dialect: DialectType = None,
7202    copy: bool = True,
7203    wrap: bool = True,
7204    **opts,
7205) -> Expression:
7206    conditions = [
7207        condition(expression, dialect=dialect, copy=copy, **opts)
7208        for expression in expressions
7209        if expression is not None
7210    ]
7211
7212    this, *rest = conditions
7213    if rest and wrap:
7214        this = _wrap(this, Connector)
7215    for expression in rest:
7216        this = operator(this=this, expression=_wrap(expression, Connector) if wrap else expression)
7217
7218    return this
7219
7220
7221@t.overload
7222def _wrap(expression: None, kind: t.Type[Expression]) -> None: ...
7223
7224
7225@t.overload
7226def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren: ...
7227
7228
7229def _wrap(expression: t.Optional[E], kind: t.Type[Expression]) -> t.Optional[E] | Paren:
7230    return Paren(this=expression) if isinstance(expression, kind) else expression
7231
7232
7233def _apply_set_operation(
7234    *expressions: ExpOrStr,
7235    set_operation: t.Type[S],
7236    distinct: bool = True,
7237    dialect: DialectType = None,
7238    copy: bool = True,
7239    **opts,
7240) -> S:
7241    return reduce(
7242        lambda x, y: set_operation(this=x, expression=y, distinct=distinct),
7243        (maybe_parse(e, dialect=dialect, copy=copy, **opts) for e in expressions),
7244    )
7245
7246
7247def union(
7248    *expressions: ExpOrStr,
7249    distinct: bool = True,
7250    dialect: DialectType = None,
7251    copy: bool = True,
7252    **opts,
7253) -> Union:
7254    """
7255    Initializes a syntax tree for the `UNION` operation.
7256
7257    Example:
7258        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
7259        'SELECT * FROM foo UNION SELECT * FROM bla'
7260
7261    Args:
7262        expressions: the SQL code strings, corresponding to the `UNION`'s operands.
7263            If `Expression` instances are passed, they will be used as-is.
7264        distinct: set the DISTINCT flag if and only if this is true.
7265        dialect: the dialect used to parse the input expression.
7266        copy: whether to copy the expression.
7267        opts: other options to use to parse the input expressions.
7268
7269    Returns:
7270        The new Union instance.
7271    """
7272    assert len(expressions) >= 2, "At least two expressions are required by `union`."
7273    return _apply_set_operation(
7274        *expressions, set_operation=Union, distinct=distinct, dialect=dialect, copy=copy, **opts
7275    )
7276
7277
7278def intersect(
7279    *expressions: ExpOrStr,
7280    distinct: bool = True,
7281    dialect: DialectType = None,
7282    copy: bool = True,
7283    **opts,
7284) -> Intersect:
7285    """
7286    Initializes a syntax tree for the `INTERSECT` operation.
7287
7288    Example:
7289        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
7290        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
7291
7292    Args:
7293        expressions: the SQL code strings, corresponding to the `INTERSECT`'s operands.
7294            If `Expression` instances are passed, they will be used as-is.
7295        distinct: set the DISTINCT flag if and only if this is true.
7296        dialect: the dialect used to parse the input expression.
7297        copy: whether to copy the expression.
7298        opts: other options to use to parse the input expressions.
7299
7300    Returns:
7301        The new Intersect instance.
7302    """
7303    assert len(expressions) >= 2, "At least two expressions are required by `intersect`."
7304    return _apply_set_operation(
7305        *expressions, set_operation=Intersect, distinct=distinct, dialect=dialect, copy=copy, **opts
7306    )
7307
7308
7309def except_(
7310    *expressions: ExpOrStr,
7311    distinct: bool = True,
7312    dialect: DialectType = None,
7313    copy: bool = True,
7314    **opts,
7315) -> Except:
7316    """
7317    Initializes a syntax tree for the `EXCEPT` operation.
7318
7319    Example:
7320        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
7321        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
7322
7323    Args:
7324        expressions: the SQL code strings, corresponding to the `EXCEPT`'s operands.
7325            If `Expression` instances are passed, they will be used as-is.
7326        distinct: set the DISTINCT flag if and only if this is true.
7327        dialect: the dialect used to parse the input expression.
7328        copy: whether to copy the expression.
7329        opts: other options to use to parse the input expressions.
7330
7331    Returns:
7332        The new Except instance.
7333    """
7334    assert len(expressions) >= 2, "At least two expressions are required by `except_`."
7335    return _apply_set_operation(
7336        *expressions, set_operation=Except, distinct=distinct, dialect=dialect, copy=copy, **opts
7337    )
7338
7339
7340def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
7341    """
7342    Initializes a syntax tree from one or multiple SELECT expressions.
7343
7344    Example:
7345        >>> select("col1", "col2").from_("tbl").sql()
7346        'SELECT col1, col2 FROM tbl'
7347
7348    Args:
7349        *expressions: the SQL code string to parse as the expressions of a
7350            SELECT statement. If an Expression instance is passed, this is used as-is.
7351        dialect: the dialect used to parse the input expressions (in the case that an
7352            input expression is a SQL string).
7353        **opts: other options to use to parse the input expressions (again, in the case
7354            that an input expression is a SQL string).
7355
7356    Returns:
7357        Select: the syntax tree for the SELECT statement.
7358    """
7359    return Select().select(*expressions, dialect=dialect, **opts)
7360
7361
7362def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
7363    """
7364    Initializes a syntax tree from a FROM expression.
7365
7366    Example:
7367        >>> from_("tbl").select("col1", "col2").sql()
7368        'SELECT col1, col2 FROM tbl'
7369
7370    Args:
7371        *expression: the SQL code string to parse as the FROM expressions of a
7372            SELECT statement. If an Expression instance is passed, this is used as-is.
7373        dialect: the dialect used to parse the input expression (in the case that the
7374            input expression is a SQL string).
7375        **opts: other options to use to parse the input expressions (again, in the case
7376            that the input expression is a SQL string).
7377
7378    Returns:
7379        Select: the syntax tree for the SELECT statement.
7380    """
7381    return Select().from_(expression, dialect=dialect, **opts)
7382
7383
7384def update(
7385    table: str | Table,
7386    properties: t.Optional[dict] = None,
7387    where: t.Optional[ExpOrStr] = None,
7388    from_: t.Optional[ExpOrStr] = None,
7389    with_: t.Optional[t.Dict[str, ExpOrStr]] = None,
7390    dialect: DialectType = None,
7391    **opts,
7392) -> Update:
7393    """
7394    Creates an update statement.
7395
7396    Example:
7397        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz_cte", where="baz_cte.id > 1 and my_table.id = baz_cte.id", with_={"baz_cte": "SELECT id FROM foo"}).sql()
7398        "WITH baz_cte AS (SELECT id FROM foo) UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz_cte WHERE baz_cte.id > 1 AND my_table.id = baz_cte.id"
7399
7400    Args:
7401        properties: dictionary of properties to SET which are
7402            auto converted to sql objects eg None -> NULL
7403        where: sql conditional parsed into a WHERE statement
7404        from_: sql statement parsed into a FROM statement
7405        with_: dictionary of CTE aliases / select statements to include in a WITH clause.
7406        dialect: the dialect used to parse the input expressions.
7407        **opts: other options to use to parse the input expressions.
7408
7409    Returns:
7410        Update: the syntax tree for the UPDATE statement.
7411    """
7412    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
7413    if properties:
7414        update_expr.set(
7415            "expressions",
7416            [
7417                EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
7418                for k, v in properties.items()
7419            ],
7420        )
7421    if from_:
7422        update_expr.set(
7423            "from",
7424            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
7425        )
7426    if isinstance(where, Condition):
7427        where = Where(this=where)
7428    if where:
7429        update_expr.set(
7430            "where",
7431            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
7432        )
7433    if with_:
7434        cte_list = [
7435            alias_(CTE(this=maybe_parse(qry, dialect=dialect, **opts)), alias, table=True)
7436            for alias, qry in with_.items()
7437        ]
7438        update_expr.set(
7439            "with",
7440            With(expressions=cte_list),
7441        )
7442    return update_expr
7443
7444
7445def delete(
7446    table: ExpOrStr,
7447    where: t.Optional[ExpOrStr] = None,
7448    returning: t.Optional[ExpOrStr] = None,
7449    dialect: DialectType = None,
7450    **opts,
7451) -> Delete:
7452    """
7453    Builds a delete statement.
7454
7455    Example:
7456        >>> delete("my_table", where="id > 1").sql()
7457        'DELETE FROM my_table WHERE id > 1'
7458
7459    Args:
7460        where: sql conditional parsed into a WHERE statement
7461        returning: sql conditional parsed into a RETURNING statement
7462        dialect: the dialect used to parse the input expressions.
7463        **opts: other options to use to parse the input expressions.
7464
7465    Returns:
7466        Delete: the syntax tree for the DELETE statement.
7467    """
7468    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
7469    if where:
7470        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
7471    if returning:
7472        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
7473    return delete_expr
7474
7475
7476def insert(
7477    expression: ExpOrStr,
7478    into: ExpOrStr,
7479    columns: t.Optional[t.Sequence[str | Identifier]] = None,
7480    overwrite: t.Optional[bool] = None,
7481    returning: t.Optional[ExpOrStr] = None,
7482    dialect: DialectType = None,
7483    copy: bool = True,
7484    **opts,
7485) -> Insert:
7486    """
7487    Builds an INSERT statement.
7488
7489    Example:
7490        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
7491        'INSERT INTO tbl VALUES (1, 2, 3)'
7492
7493    Args:
7494        expression: the sql string or expression of the INSERT statement
7495        into: the tbl to insert data to.
7496        columns: optionally the table's column names.
7497        overwrite: whether to INSERT OVERWRITE or not.
7498        returning: sql conditional parsed into a RETURNING statement
7499        dialect: the dialect used to parse the input expressions.
7500        copy: whether to copy the expression.
7501        **opts: other options to use to parse the input expressions.
7502
7503    Returns:
7504        Insert: the syntax tree for the INSERT statement.
7505    """
7506    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
7507    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
7508
7509    if columns:
7510        this = Schema(this=this, expressions=[to_identifier(c, copy=copy) for c in columns])
7511
7512    insert = Insert(this=this, expression=expr, overwrite=overwrite)
7513
7514    if returning:
7515        insert = insert.returning(returning, dialect=dialect, copy=False, **opts)
7516
7517    return insert
7518
7519
7520def merge(
7521    *when_exprs: ExpOrStr,
7522    into: ExpOrStr,
7523    using: ExpOrStr,
7524    on: ExpOrStr,
7525    returning: t.Optional[ExpOrStr] = None,
7526    dialect: DialectType = None,
7527    copy: bool = True,
7528    **opts,
7529) -> Merge:
7530    """
7531    Builds a MERGE statement.
7532
7533    Example:
7534        >>> merge("WHEN MATCHED THEN UPDATE SET col1 = source_table.col1",
7535        ...       "WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)",
7536        ...       into="my_table",
7537        ...       using="source_table",
7538        ...       on="my_table.id = source_table.id").sql()
7539        'MERGE INTO my_table USING source_table ON my_table.id = source_table.id WHEN MATCHED THEN UPDATE SET col1 = source_table.col1 WHEN NOT MATCHED THEN INSERT (col1) VALUES (source_table.col1)'
7540
7541    Args:
7542        *when_exprs: The WHEN clauses specifying actions for matched and unmatched rows.
7543        into: The target table to merge data into.
7544        using: The source table to merge data from.
7545        on: The join condition for the merge.
7546        returning: The columns to return from the merge.
7547        dialect: The dialect used to parse the input expressions.
7548        copy: Whether to copy the expression.
7549        **opts: Other options to use to parse the input expressions.
7550
7551    Returns:
7552        Merge: The syntax tree for the MERGE statement.
7553    """
7554    expressions: t.List[Expression] = []
7555    for when_expr in when_exprs:
7556        expression = maybe_parse(when_expr, dialect=dialect, copy=copy, into=Whens, **opts)
7557        expressions.extend([expression] if isinstance(expression, When) else expression.expressions)
7558
7559    merge = Merge(
7560        this=maybe_parse(into, dialect=dialect, copy=copy, **opts),
7561        using=maybe_parse(using, dialect=dialect, copy=copy, **opts),
7562        on=maybe_parse(on, dialect=dialect, copy=copy, **opts),
7563        whens=Whens(expressions=expressions),
7564    )
7565    if returning:
7566        merge = merge.returning(returning, dialect=dialect, copy=False, **opts)
7567
7568    return merge
7569
7570
7571def condition(
7572    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
7573) -> Condition:
7574    """
7575    Initialize a logical condition expression.
7576
7577    Example:
7578        >>> condition("x=1").sql()
7579        'x = 1'
7580
7581        This is helpful for composing larger logical syntax trees:
7582        >>> where = condition("x=1")
7583        >>> where = where.and_("y=1")
7584        >>> Select().from_("tbl").select("*").where(where).sql()
7585        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
7586
7587    Args:
7588        *expression: the SQL code string to parse.
7589            If an Expression instance is passed, this is used as-is.
7590        dialect: the dialect used to parse the input expression (in the case that the
7591            input expression is a SQL string).
7592        copy: Whether to copy `expression` (only applies to expressions).
7593        **opts: other options to use to parse the input expressions (again, in the case
7594            that the input expression is a SQL string).
7595
7596    Returns:
7597        The new Condition instance
7598    """
7599    return maybe_parse(
7600        expression,
7601        into=Condition,
7602        dialect=dialect,
7603        copy=copy,
7604        **opts,
7605    )
7606
7607
7608def and_(
7609    *expressions: t.Optional[ExpOrStr],
7610    dialect: DialectType = None,
7611    copy: bool = True,
7612    wrap: bool = True,
7613    **opts,
7614) -> Condition:
7615    """
7616    Combine multiple conditions with an AND logical operator.
7617
7618    Example:
7619        >>> and_("x=1", and_("y=1", "z=1")).sql()
7620        'x = 1 AND (y = 1 AND z = 1)'
7621
7622    Args:
7623        *expressions: the SQL code strings to parse.
7624            If an Expression instance is passed, this is used as-is.
7625        dialect: the dialect used to parse the input expression.
7626        copy: whether to copy `expressions` (only applies to Expressions).
7627        wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid
7628            precedence issues, but can be turned off when the produced AST is too deep and
7629            causes recursion-related issues.
7630        **opts: other options to use to parse the input expressions.
7631
7632    Returns:
7633        The new condition
7634    """
7635    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, wrap=wrap, **opts))
7636
7637
7638def or_(
7639    *expressions: t.Optional[ExpOrStr],
7640    dialect: DialectType = None,
7641    copy: bool = True,
7642    wrap: bool = True,
7643    **opts,
7644) -> Condition:
7645    """
7646    Combine multiple conditions with an OR logical operator.
7647
7648    Example:
7649        >>> or_("x=1", or_("y=1", "z=1")).sql()
7650        'x = 1 OR (y = 1 OR z = 1)'
7651
7652    Args:
7653        *expressions: the SQL code strings to parse.
7654            If an Expression instance is passed, this is used as-is.
7655        dialect: the dialect used to parse the input expression.
7656        copy: whether to copy `expressions` (only applies to Expressions).
7657        wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid
7658            precedence issues, but can be turned off when the produced AST is too deep and
7659            causes recursion-related issues.
7660        **opts: other options to use to parse the input expressions.
7661
7662    Returns:
7663        The new condition
7664    """
7665    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, wrap=wrap, **opts))
7666
7667
7668def xor(
7669    *expressions: t.Optional[ExpOrStr],
7670    dialect: DialectType = None,
7671    copy: bool = True,
7672    wrap: bool = True,
7673    **opts,
7674) -> Condition:
7675    """
7676    Combine multiple conditions with an XOR logical operator.
7677
7678    Example:
7679        >>> xor("x=1", xor("y=1", "z=1")).sql()
7680        'x = 1 XOR (y = 1 XOR z = 1)'
7681
7682    Args:
7683        *expressions: the SQL code strings to parse.
7684            If an Expression instance is passed, this is used as-is.
7685        dialect: the dialect used to parse the input expression.
7686        copy: whether to copy `expressions` (only applies to Expressions).
7687        wrap: whether to wrap the operands in `Paren`s. This is true by default to avoid
7688            precedence issues, but can be turned off when the produced AST is too deep and
7689            causes recursion-related issues.
7690        **opts: other options to use to parse the input expressions.
7691
7692    Returns:
7693        The new condition
7694    """
7695    return t.cast(Condition, _combine(expressions, Xor, dialect, copy=copy, wrap=wrap, **opts))
7696
7697
7698def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
7699    """
7700    Wrap a condition with a NOT operator.
7701
7702    Example:
7703        >>> not_("this_suit='black'").sql()
7704        "NOT this_suit = 'black'"
7705
7706    Args:
7707        expression: the SQL code string to parse.
7708            If an Expression instance is passed, this is used as-is.
7709        dialect: the dialect used to parse the input expression.
7710        copy: whether to copy the expression or not.
7711        **opts: other options to use to parse the input expressions.
7712
7713    Returns:
7714        The new condition.
7715    """
7716    this = condition(
7717        expression,
7718        dialect=dialect,
7719        copy=copy,
7720        **opts,
7721    )
7722    return Not(this=_wrap(this, Connector))
7723
7724
7725def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
7726    """
7727    Wrap an expression in parentheses.
7728
7729    Example:
7730        >>> paren("5 + 3").sql()
7731        '(5 + 3)'
7732
7733    Args:
7734        expression: the SQL code string to parse.
7735            If an Expression instance is passed, this is used as-is.
7736        copy: whether to copy the expression or not.
7737
7738    Returns:
7739        The wrapped expression.
7740    """
7741    return Paren(this=maybe_parse(expression, copy=copy))
7742
7743
7744SAFE_IDENTIFIER_RE: t.Pattern[str] = re.compile(r"^[_a-zA-Z][\w]*$")
7745
7746
7747@t.overload
7748def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None: ...
7749
7750
7751@t.overload
7752def to_identifier(
7753    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
7754) -> Identifier: ...
7755
7756
7757def to_identifier(name, quoted=None, copy=True):
7758    """Builds an identifier.
7759
7760    Args:
7761        name: The name to turn into an identifier.
7762        quoted: Whether to force quote the identifier.
7763        copy: Whether to copy name if it's an Identifier.
7764
7765    Returns:
7766        The identifier ast node.
7767    """
7768
7769    if name is None:
7770        return None
7771
7772    if isinstance(name, Identifier):
7773        identifier = maybe_copy(name, copy)
7774    elif isinstance(name, str):
7775        identifier = Identifier(
7776            this=name,
7777            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
7778        )
7779    else:
7780        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
7781    return identifier
7782
7783
7784def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
7785    """
7786    Parses a given string into an identifier.
7787
7788    Args:
7789        name: The name to parse into an identifier.
7790        dialect: The dialect to parse against.
7791
7792    Returns:
7793        The identifier ast node.
7794    """
7795    try:
7796        expression = maybe_parse(name, dialect=dialect, into=Identifier)
7797    except (ParseError, TokenError):
7798        expression = to_identifier(name)
7799
7800    return expression
7801
7802
7803INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
7804
7805
7806def to_interval(interval: str | Literal) -> Interval:
7807    """Builds an interval expression from a string like '1 day' or '5 months'."""
7808    if isinstance(interval, Literal):
7809        if not interval.is_string:
7810            raise ValueError("Invalid interval string.")
7811
7812        interval = interval.this
7813
7814    interval = maybe_parse(f"INTERVAL {interval}")
7815    assert isinstance(interval, Interval)
7816    return interval
7817
7818
7819def to_table(
7820    sql_path: str | Table, dialect: DialectType = None, copy: bool = True, **kwargs
7821) -> Table:
7822    """
7823    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
7824    If a table is passed in then that table is returned.
7825
7826    Args:
7827        sql_path: a `[catalog].[schema].[table]` string.
7828        dialect: the source dialect according to which the table name will be parsed.
7829        copy: Whether to copy a table if it is passed in.
7830        kwargs: the kwargs to instantiate the resulting `Table` expression with.
7831
7832    Returns:
7833        A table expression.
7834    """
7835    if isinstance(sql_path, Table):
7836        return maybe_copy(sql_path, copy=copy)
7837
7838    table = maybe_parse(sql_path, into=Table, dialect=dialect)
7839
7840    for k, v in kwargs.items():
7841        table.set(k, v)
7842
7843    return table
7844
7845
7846def to_column(
7847    sql_path: str | Column,
7848    quoted: t.Optional[bool] = None,
7849    dialect: DialectType = None,
7850    copy: bool = True,
7851    **kwargs,
7852) -> Column:
7853    """
7854    Create a column from a `[table].[column]` sql path. Table is optional.
7855    If a column is passed in then that column is returned.
7856
7857    Args:
7858        sql_path: a `[table].[column]` string.
7859        quoted: Whether or not to force quote identifiers.
7860        dialect: the source dialect according to which the column name will be parsed.
7861        copy: Whether to copy a column if it is passed in.
7862        kwargs: the kwargs to instantiate the resulting `Column` expression with.
7863
7864    Returns:
7865        A column expression.
7866    """
7867    if isinstance(sql_path, Column):
7868        return maybe_copy(sql_path, copy=copy)
7869
7870    try:
7871        col = maybe_parse(sql_path, into=Column, dialect=dialect)
7872    except ParseError:
7873        return column(*reversed(sql_path.split(".")), quoted=quoted, **kwargs)
7874
7875    for k, v in kwargs.items():
7876        col.set(k, v)
7877
7878    if quoted:
7879        for i in col.find_all(Identifier):
7880            i.set("quoted", True)
7881
7882    return col
7883
7884
7885def alias_(
7886    expression: ExpOrStr,
7887    alias: t.Optional[str | Identifier],
7888    table: bool | t.Sequence[str | Identifier] = False,
7889    quoted: t.Optional[bool] = None,
7890    dialect: DialectType = None,
7891    copy: bool = True,
7892    **opts,
7893):
7894    """Create an Alias expression.
7895
7896    Example:
7897        >>> alias_('foo', 'bar').sql()
7898        'foo AS bar'
7899
7900        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
7901        '(SELECT 1, 2) AS bar(a, b)'
7902
7903    Args:
7904        expression: the SQL code strings to parse.
7905            If an Expression instance is passed, this is used as-is.
7906        alias: the alias name to use. If the name has
7907            special characters it is quoted.
7908        table: Whether to create a table alias, can also be a list of columns.
7909        quoted: whether to quote the alias
7910        dialect: the dialect used to parse the input expression.
7911        copy: Whether to copy the expression.
7912        **opts: other options to use to parse the input expressions.
7913
7914    Returns:
7915        Alias: the aliased expression
7916    """
7917    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
7918    alias = to_identifier(alias, quoted=quoted)
7919
7920    if table:
7921        table_alias = TableAlias(this=alias)
7922        exp.set("alias", table_alias)
7923
7924        if not isinstance(table, bool):
7925            for column in table:
7926                table_alias.append("columns", to_identifier(column, quoted=quoted))
7927
7928        return exp
7929
7930    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
7931    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
7932    # for the complete Window expression.
7933    #
7934    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
7935
7936    if "alias" in exp.arg_types and not isinstance(exp, Window):
7937        exp.set("alias", alias)
7938        return exp
7939    return Alias(this=exp, alias=alias)
7940
7941
7942def subquery(
7943    expression: ExpOrStr,
7944    alias: t.Optional[Identifier | str] = None,
7945    dialect: DialectType = None,
7946    **opts,
7947) -> Select:
7948    """
7949    Build a subquery expression that's selected from.
7950
7951    Example:
7952        >>> subquery('select x from tbl', 'bar').select('x').sql()
7953        'SELECT x FROM (SELECT x FROM tbl) AS bar'
7954
7955    Args:
7956        expression: the SQL code strings to parse.
7957            If an Expression instance is passed, this is used as-is.
7958        alias: the alias name to use.
7959        dialect: the dialect used to parse the input expression.
7960        **opts: other options to use to parse the input expressions.
7961
7962    Returns:
7963        A new Select instance with the subquery expression included.
7964    """
7965
7966    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias, **opts)
7967    return Select().from_(expression, dialect=dialect, **opts)
7968
7969
7970@t.overload
7971def column(
7972    col: str | Identifier,
7973    table: t.Optional[str | Identifier] = None,
7974    db: t.Optional[str | Identifier] = None,
7975    catalog: t.Optional[str | Identifier] = None,
7976    *,
7977    fields: t.Collection[t.Union[str, Identifier]],
7978    quoted: t.Optional[bool] = None,
7979    copy: bool = True,
7980) -> Dot:
7981    pass
7982
7983
7984@t.overload
7985def column(
7986    col: str | Identifier,
7987    table: t.Optional[str | Identifier] = None,
7988    db: t.Optional[str | Identifier] = None,
7989    catalog: t.Optional[str | Identifier] = None,
7990    *,
7991    fields: Lit[None] = None,
7992    quoted: t.Optional[bool] = None,
7993    copy: bool = True,
7994) -> Column:
7995    pass
7996
7997
7998def column(
7999    col,
8000    table=None,
8001    db=None,
8002    catalog=None,
8003    *,
8004    fields=None,
8005    quoted=None,
8006    copy=True,
8007):
8008    """
8009    Build a Column.
8010
8011    Args:
8012        col: Column name.
8013        table: Table name.
8014        db: Database name.
8015        catalog: Catalog name.
8016        fields: Additional fields using dots.
8017        quoted: Whether to force quotes on the column's identifiers.
8018        copy: Whether to copy identifiers if passed in.
8019
8020    Returns:
8021        The new Column instance.
8022    """
8023    this = Column(
8024        this=to_identifier(col, quoted=quoted, copy=copy),
8025        table=to_identifier(table, quoted=quoted, copy=copy),
8026        db=to_identifier(db, quoted=quoted, copy=copy),
8027        catalog=to_identifier(catalog, quoted=quoted, copy=copy),
8028    )
8029
8030    if fields:
8031        this = Dot.build(
8032            (this, *(to_identifier(field, quoted=quoted, copy=copy) for field in fields))
8033        )
8034    return this
8035
8036
8037def cast(
8038    expression: ExpOrStr, to: DATA_TYPE, copy: bool = True, dialect: DialectType = None, **opts
8039) -> Cast:
8040    """Cast an expression to a data type.
8041
8042    Example:
8043        >>> cast('x + 1', 'int').sql()
8044        'CAST(x + 1 AS INT)'
8045
8046    Args:
8047        expression: The expression to cast.
8048        to: The datatype to cast to.
8049        copy: Whether to copy the supplied expressions.
8050        dialect: The target dialect. This is used to prevent a re-cast in the following scenario:
8051            - The expression to be cast is already a exp.Cast expression
8052            - The existing cast is to a type that is logically equivalent to new type
8053
8054            For example, if :expression='CAST(x as DATETIME)' and :to=Type.TIMESTAMP,
8055            but in the target dialect DATETIME is mapped to TIMESTAMP, then we will NOT return `CAST(x (as DATETIME) as TIMESTAMP)`
8056            and instead just return the original expression `CAST(x as DATETIME)`.
8057
8058            This is to prevent it being output as a double cast `CAST(x (as TIMESTAMP) as TIMESTAMP)` once the DATETIME -> TIMESTAMP
8059            mapping is applied in the target dialect generator.
8060
8061    Returns:
8062        The new Cast instance.
8063    """
8064    expr = maybe_parse(expression, copy=copy, dialect=dialect, **opts)
8065    data_type = DataType.build(to, copy=copy, dialect=dialect, **opts)
8066
8067    # dont re-cast if the expression is already a cast to the correct type
8068    if isinstance(expr, Cast):
8069        from sqlglot.dialects.dialect import Dialect
8070
8071        target_dialect = Dialect.get_or_raise(dialect)
8072        type_mapping = target_dialect.generator_class.TYPE_MAPPING
8073
8074        existing_cast_type: DataType.Type = expr.to.this
8075        new_cast_type: DataType.Type = data_type.this
8076        types_are_equivalent = type_mapping.get(
8077            existing_cast_type, existing_cast_type.value
8078        ) == type_mapping.get(new_cast_type, new_cast_type.value)
8079
8080        if expr.is_type(data_type) or types_are_equivalent:
8081            return expr
8082
8083    expr = Cast(this=expr, to=data_type)
8084    expr.type = data_type
8085
8086    return expr
8087
8088
8089def table_(
8090    table: Identifier | str,
8091    db: t.Optional[Identifier | str] = None,
8092    catalog: t.Optional[Identifier | str] = None,
8093    quoted: t.Optional[bool] = None,
8094    alias: t.Optional[Identifier | str] = None,
8095) -> Table:
8096    """Build a Table.
8097
8098    Args:
8099        table: Table name.
8100        db: Database name.
8101        catalog: Catalog name.
8102        quote: Whether to force quotes on the table's identifiers.
8103        alias: Table's alias.
8104
8105    Returns:
8106        The new Table instance.
8107    """
8108    return Table(
8109        this=to_identifier(table, quoted=quoted) if table else None,
8110        db=to_identifier(db, quoted=quoted) if db else None,
8111        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
8112        alias=TableAlias(this=to_identifier(alias)) if alias else None,
8113    )
8114
8115
8116def values(
8117    values: t.Iterable[t.Tuple[t.Any, ...]],
8118    alias: t.Optional[str] = None,
8119    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
8120) -> Values:
8121    """Build VALUES statement.
8122
8123    Example:
8124        >>> values([(1, '2')]).sql()
8125        "VALUES (1, '2')"
8126
8127    Args:
8128        values: values statements that will be converted to SQL
8129        alias: optional alias
8130        columns: Optional list of ordered column names or ordered dictionary of column names to types.
8131         If either are provided then an alias is also required.
8132
8133    Returns:
8134        Values: the Values expression object
8135    """
8136    if columns and not alias:
8137        raise ValueError("Alias is required when providing columns")
8138
8139    return Values(
8140        expressions=[convert(tup) for tup in values],
8141        alias=(
8142            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
8143            if columns
8144            else (TableAlias(this=to_identifier(alias)) if alias else None)
8145        ),
8146    )
8147
8148
8149def var(name: t.Optional[ExpOrStr]) -> Var:
8150    """Build a SQL variable.
8151
8152    Example:
8153        >>> repr(var('x'))
8154        'Var(this=x)'
8155
8156        >>> repr(var(column('x', table='y')))
8157        'Var(this=x)'
8158
8159    Args:
8160        name: The name of the var or an expression who's name will become the var.
8161
8162    Returns:
8163        The new variable node.
8164    """
8165    if not name:
8166        raise ValueError("Cannot convert empty name into var.")
8167
8168    if isinstance(name, Expression):
8169        name = name.name
8170    return Var(this=name)
8171
8172
8173def rename_table(
8174    old_name: str | Table,
8175    new_name: str | Table,
8176    dialect: DialectType = None,
8177) -> Alter:
8178    """Build ALTER TABLE... RENAME... expression
8179
8180    Args:
8181        old_name: The old name of the table
8182        new_name: The new name of the table
8183        dialect: The dialect to parse the table.
8184
8185    Returns:
8186        Alter table expression
8187    """
8188    old_table = to_table(old_name, dialect=dialect)
8189    new_table = to_table(new_name, dialect=dialect)
8190    return Alter(
8191        this=old_table,
8192        kind="TABLE",
8193        actions=[
8194            AlterRename(this=new_table),
8195        ],
8196    )
8197
8198
8199def rename_column(
8200    table_name: str | Table,
8201    old_column_name: str | Column,
8202    new_column_name: str | Column,
8203    exists: t.Optional[bool] = None,
8204    dialect: DialectType = None,
8205) -> Alter:
8206    """Build ALTER TABLE... RENAME COLUMN... expression
8207
8208    Args:
8209        table_name: Name of the table
8210        old_column: The old name of the column
8211        new_column: The new name of the column
8212        exists: Whether to add the `IF EXISTS` clause
8213        dialect: The dialect to parse the table/column.
8214
8215    Returns:
8216        Alter table expression
8217    """
8218    table = to_table(table_name, dialect=dialect)
8219    old_column = to_column(old_column_name, dialect=dialect)
8220    new_column = to_column(new_column_name, dialect=dialect)
8221    return Alter(
8222        this=table,
8223        kind="TABLE",
8224        actions=[
8225            RenameColumn(this=old_column, to=new_column, exists=exists),
8226        ],
8227    )
8228
8229
8230def convert(value: t.Any, copy: bool = False) -> Expression:
8231    """Convert a python value into an expression object.
8232
8233    Raises an error if a conversion is not possible.
8234
8235    Args:
8236        value: A python object.
8237        copy: Whether to copy `value` (only applies to Expressions and collections).
8238
8239    Returns:
8240        The equivalent expression object.
8241    """
8242    if isinstance(value, Expression):
8243        return maybe_copy(value, copy)
8244    if isinstance(value, str):
8245        return Literal.string(value)
8246    if isinstance(value, bool):
8247        return Boolean(this=value)
8248    if value is None or (isinstance(value, float) and math.isnan(value)):
8249        return null()
8250    if isinstance(value, numbers.Number):
8251        return Literal.number(value)
8252    if isinstance(value, bytes):
8253        return HexString(this=value.hex())
8254    if isinstance(value, datetime.datetime):
8255        datetime_literal = Literal.string(value.isoformat(sep=" "))
8256
8257        tz = None
8258        if value.tzinfo:
8259            # this works for zoneinfo.ZoneInfo, pytz.timezone and datetime.datetime.utc to return IANA timezone names like "America/Los_Angeles"
8260            # instead of abbreviations like "PDT". This is for consistency with other timezone handling functions in SQLGlot
8261            tz = Literal.string(str(value.tzinfo))
8262
8263        return TimeStrToTime(this=datetime_literal, zone=tz)
8264    if isinstance(value, datetime.date):
8265        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
8266        return DateStrToDate(this=date_literal)
8267    if isinstance(value, tuple):
8268        if hasattr(value, "_fields"):
8269            return Struct(
8270                expressions=[
8271                    PropertyEQ(
8272                        this=to_identifier(k), expression=convert(getattr(value, k), copy=copy)
8273                    )
8274                    for k in value._fields
8275                ]
8276            )
8277        return Tuple(expressions=[convert(v, copy=copy) for v in value])
8278    if isinstance(value, list):
8279        return Array(expressions=[convert(v, copy=copy) for v in value])
8280    if isinstance(value, dict):
8281        return Map(
8282            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
8283            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
8284        )
8285    if hasattr(value, "__dict__"):
8286        return Struct(
8287            expressions=[
8288                PropertyEQ(this=to_identifier(k), expression=convert(v, copy=copy))
8289                for k, v in value.__dict__.items()
8290            ]
8291        )
8292    raise ValueError(f"Cannot convert {value}")
8293
8294
8295def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
8296    """
8297    Replace children of an expression with the result of a lambda fun(child) -> exp.
8298    """
8299    for k, v in tuple(expression.args.items()):
8300        is_list_arg = type(v) is list
8301
8302        child_nodes = v if is_list_arg else [v]
8303        new_child_nodes = []
8304
8305        for cn in child_nodes:
8306            if isinstance(cn, Expression):
8307                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
8308                    new_child_nodes.append(child_node)
8309            else:
8310                new_child_nodes.append(cn)
8311
8312        expression.set(k, new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0))
8313
8314
8315def replace_tree(
8316    expression: Expression,
8317    fun: t.Callable,
8318    prune: t.Optional[t.Callable[[Expression], bool]] = None,
8319) -> Expression:
8320    """
8321    Replace an entire tree with the result of function calls on each node.
8322
8323    This will be traversed in reverse dfs, so leaves first.
8324    If new nodes are created as a result of function calls, they will also be traversed.
8325    """
8326    stack = list(expression.dfs(prune=prune))
8327
8328    while stack:
8329        node = stack.pop()
8330        new_node = fun(node)
8331
8332        if new_node is not node:
8333            node.replace(new_node)
8334
8335            if isinstance(new_node, Expression):
8336                stack.append(new_node)
8337
8338    return new_node
8339
8340
8341def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
8342    """
8343    Return all table names referenced through columns in an expression.
8344
8345    Example:
8346        >>> import sqlglot
8347        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
8348        ['a', 'c']
8349
8350    Args:
8351        expression: expression to find table names.
8352        exclude: a table name to exclude
8353
8354    Returns:
8355        A list of unique names.
8356    """
8357    return {
8358        table
8359        for table in (column.table for column in expression.find_all(Column))
8360        if table and table != exclude
8361    }
8362
8363
8364def table_name(table: Table | str, dialect: DialectType = None, identify: bool = False) -> str:
8365    """Get the full name of a table as a string.
8366
8367    Args:
8368        table: Table expression node or string.
8369        dialect: The dialect to generate the table name for.
8370        identify: Determines when an identifier should be quoted. Possible values are:
8371            False (default): Never quote, except in cases where it's mandatory by the dialect.
8372            True: Always quote.
8373
8374    Examples:
8375        >>> from sqlglot import exp, parse_one
8376        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
8377        'a.b.c'
8378
8379    Returns:
8380        The table name.
8381    """
8382
8383    table = maybe_parse(table, into=Table, dialect=dialect)
8384
8385    if not table:
8386        raise ValueError(f"Cannot parse {table}")
8387
8388    return ".".join(
8389        (
8390            part.sql(dialect=dialect, identify=True, copy=False, comments=False)
8391            if identify or not SAFE_IDENTIFIER_RE.match(part.name)
8392            else part.name
8393        )
8394        for part in table.parts
8395    )
8396
8397
8398def normalize_table_name(table: str | Table, dialect: DialectType = None, copy: bool = True) -> str:
8399    """Returns a case normalized table name without quotes.
8400
8401    Args:
8402        table: the table to normalize
8403        dialect: the dialect to use for normalization rules
8404        copy: whether to copy the expression.
8405
8406    Examples:
8407        >>> normalize_table_name("`A-B`.c", dialect="bigquery")
8408        'A-B.c'
8409    """
8410    from sqlglot.optimizer.normalize_identifiers import normalize_identifiers
8411
8412    return ".".join(
8413        p.name
8414        for p in normalize_identifiers(
8415            to_table(table, dialect=dialect, copy=copy), dialect=dialect
8416        ).parts
8417    )
8418
8419
8420def replace_tables(
8421    expression: E, mapping: t.Dict[str, str], dialect: DialectType = None, copy: bool = True
8422) -> E:
8423    """Replace all tables in expression according to the mapping.
8424
8425    Args:
8426        expression: expression node to be transformed and replaced.
8427        mapping: mapping of table names.
8428        dialect: the dialect of the mapping table
8429        copy: whether to copy the expression.
8430
8431    Examples:
8432        >>> from sqlglot import exp, parse_one
8433        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
8434        'SELECT * FROM c /* a.b */'
8435
8436    Returns:
8437        The mapped expression.
8438    """
8439
8440    mapping = {normalize_table_name(k, dialect=dialect): v for k, v in mapping.items()}
8441
8442    def _replace_tables(node: Expression) -> Expression:
8443        if isinstance(node, Table) and node.meta.get("replace") is not False:
8444            original = normalize_table_name(node, dialect=dialect)
8445            new_name = mapping.get(original)
8446
8447            if new_name:
8448                table = to_table(
8449                    new_name,
8450                    **{k: v for k, v in node.args.items() if k not in TABLE_PARTS},
8451                    dialect=dialect,
8452                )
8453                table.add_comments([original])
8454                return table
8455        return node
8456
8457    return expression.transform(_replace_tables, copy=copy)  # type: ignore
8458
8459
8460def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
8461    """Replace placeholders in an expression.
8462
8463    Args:
8464        expression: expression node to be transformed and replaced.
8465        args: positional names that will substitute unnamed placeholders in the given order.
8466        kwargs: keyword arguments that will substitute named placeholders.
8467
8468    Examples:
8469        >>> from sqlglot import exp, parse_one
8470        >>> replace_placeholders(
8471        ...     parse_one("select * from :tbl where ? = ?"),
8472        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
8473        ... ).sql()
8474        "SELECT * FROM foo WHERE str_col = 'b'"
8475
8476    Returns:
8477        The mapped expression.
8478    """
8479
8480    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
8481        if isinstance(node, Placeholder):
8482            if node.this:
8483                new_name = kwargs.get(node.this)
8484                if new_name is not None:
8485                    return convert(new_name)
8486            else:
8487                try:
8488                    return convert(next(args))
8489                except StopIteration:
8490                    pass
8491        return node
8492
8493    return expression.transform(_replace_placeholders, iter(args), **kwargs)
8494
8495
8496def expand(
8497    expression: Expression,
8498    sources: t.Dict[str, Query],
8499    dialect: DialectType = None,
8500    copy: bool = True,
8501) -> Expression:
8502    """Transforms an expression by expanding all referenced sources into subqueries.
8503
8504    Examples:
8505        >>> from sqlglot import parse_one
8506        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
8507        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
8508
8509        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
8510        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
8511
8512    Args:
8513        expression: The expression to expand.
8514        sources: A dictionary of name to Queries.
8515        dialect: The dialect of the sources dict.
8516        copy: Whether to copy the expression during transformation. Defaults to True.
8517
8518    Returns:
8519        The transformed expression.
8520    """
8521    sources = {normalize_table_name(k, dialect=dialect): v for k, v in sources.items()}
8522
8523    def _expand(node: Expression):
8524        if isinstance(node, Table):
8525            name = normalize_table_name(node, dialect=dialect)
8526            source = sources.get(name)
8527            if source:
8528                subquery = source.subquery(node.alias or name)
8529                subquery.comments = [f"source: {name}"]
8530                return subquery.transform(_expand, copy=False)
8531        return node
8532
8533    return expression.transform(_expand, copy=copy)
8534
8535
8536def func(name: str, *args, copy: bool = True, dialect: DialectType = None, **kwargs) -> Func:
8537    """
8538    Returns a Func expression.
8539
8540    Examples:
8541        >>> func("abs", 5).sql()
8542        'ABS(5)'
8543
8544        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
8545        'CAST(5 AS DOUBLE)'
8546
8547    Args:
8548        name: the name of the function to build.
8549        args: the args used to instantiate the function of interest.
8550        copy: whether to copy the argument expressions.
8551        dialect: the source dialect.
8552        kwargs: the kwargs used to instantiate the function of interest.
8553
8554    Note:
8555        The arguments `args` and `kwargs` are mutually exclusive.
8556
8557    Returns:
8558        An instance of the function of interest, or an anonymous function, if `name` doesn't
8559        correspond to an existing `sqlglot.expressions.Func` class.
8560    """
8561    if args and kwargs:
8562        raise ValueError("Can't use both args and kwargs to instantiate a function.")
8563
8564    from sqlglot.dialects.dialect import Dialect
8565
8566    dialect = Dialect.get_or_raise(dialect)
8567
8568    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect, copy=copy) for arg in args]
8569    kwargs = {key: maybe_parse(value, dialect=dialect, copy=copy) for key, value in kwargs.items()}
8570
8571    constructor = dialect.parser_class.FUNCTIONS.get(name.upper())
8572    if constructor:
8573        if converted:
8574            if "dialect" in constructor.__code__.co_varnames:
8575                function = constructor(converted, dialect=dialect)
8576            else:
8577                function = constructor(converted)
8578        elif constructor.__name__ == "from_arg_list":
8579            function = constructor.__self__(**kwargs)  # type: ignore
8580        else:
8581            constructor = FUNCTION_BY_NAME.get(name.upper())
8582            if constructor:
8583                function = constructor(**kwargs)
8584            else:
8585                raise ValueError(
8586                    f"Unable to convert '{name}' into a Func. Either manually construct "
8587                    "the Func expression of interest or parse the function call."
8588                )
8589    else:
8590        kwargs = kwargs or {"expressions": converted}
8591        function = Anonymous(this=name, **kwargs)
8592
8593    for error_message in function.error_messages(converted):
8594        raise ValueError(error_message)
8595
8596    return function
8597
8598
8599def case(
8600    expression: t.Optional[ExpOrStr] = None,
8601    **opts,
8602) -> Case:
8603    """
8604    Initialize a CASE statement.
8605
8606    Example:
8607        case().when("a = 1", "foo").else_("bar")
8608
8609    Args:
8610        expression: Optionally, the input expression (not all dialects support this)
8611        **opts: Extra keyword arguments for parsing `expression`
8612    """
8613    if expression is not None:
8614        this = maybe_parse(expression, **opts)
8615    else:
8616        this = None
8617    return Case(this=this, ifs=[])
8618
8619
8620def array(
8621    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
8622) -> Array:
8623    """
8624    Returns an array.
8625
8626    Examples:
8627        >>> array(1, 'x').sql()
8628        'ARRAY(1, x)'
8629
8630    Args:
8631        expressions: the expressions to add to the array.
8632        copy: whether to copy the argument expressions.
8633        dialect: the source dialect.
8634        kwargs: the kwargs used to instantiate the function of interest.
8635
8636    Returns:
8637        An array expression.
8638    """
8639    return Array(
8640        expressions=[
8641            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
8642            for expression in expressions
8643        ]
8644    )
8645
8646
8647def tuple_(
8648    *expressions: ExpOrStr, copy: bool = True, dialect: DialectType = None, **kwargs
8649) -> Tuple:
8650    """
8651    Returns an tuple.
8652
8653    Examples:
8654        >>> tuple_(1, 'x').sql()
8655        '(1, x)'
8656
8657    Args:
8658        expressions: the expressions to add to the tuple.
8659        copy: whether to copy the argument expressions.
8660        dialect: the source dialect.
8661        kwargs: the kwargs used to instantiate the function of interest.
8662
8663    Returns:
8664        A tuple expression.
8665    """
8666    return Tuple(
8667        expressions=[
8668            maybe_parse(expression, copy=copy, dialect=dialect, **kwargs)
8669            for expression in expressions
8670        ]
8671    )
8672
8673
8674def true() -> Boolean:
8675    """
8676    Returns a true Boolean expression.
8677    """
8678    return Boolean(this=True)
8679
8680
8681def false() -> Boolean:
8682    """
8683    Returns a false Boolean expression.
8684    """
8685    return Boolean(this=False)
8686
8687
8688def null() -> Null:
8689    """
8690    Returns a Null expression.
8691    """
8692    return Null()
8693
8694
8695NONNULL_CONSTANTS = (
8696    Literal,
8697    Boolean,
8698)
8699
8700CONSTANTS = (
8701    Literal,
8702    Boolean,
8703    Null,
8704)